path: root/import/import.py

#!/usr/bin/env python3

import sys
import os
import json
import re
import pymysql
import configparser

def default(x, default):
    return default if x is None else x

with open(sys.argv[1], 'r') as f:
    data = json.load(f)

c = configparser.ConfigParser()
c.read('config.ini')

filename = os.path.basename(sys.argv[1])

date = re.search('^[0-9]{4}-[0-9]{2}-[0-9]{2}', filename).group(0)
ip = re.search('_([0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3})_', filename).group(1)

db = pymysql.connect(c.get('db', 'host'), c.get('db', 'username'), c.get('db', 'password'), c.get('db', 'name'), cursorclass=pymysql.cursors.DictCursor)
cursor = db.cursor()

# Add report to database
try:
    cursor.execute('INSERT INTO reports(date,ip,version) VALUES(%s, %s, %s)',(
            date,
            ip,
            data['version']
        ))
except pymysql.err.IntegrityError:
    print('Report is already in database!')
    exit(1)
cursor.execute('SELECT LAST_INSERT_ID() as id')
reportId = cursor.fetchone()['id']

# Add server information to database
cursor.execute('INSERT INTO server(report, cpuCount, cpuModel, uptime, memTotal, memFree, swapTotal, swapUsed) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',(
        reportId,
        data['server']['cpuCount'],
        data['server']['cpuModel'],
        data['server']['uptime'],
        data['server']['memTotal'],
        data['server']['memFree'],
        data['server']['swapTotal'],
        data['server']['swapUsed']
    ))

# Loop timeframes
for i in [7, 30, 90]:
    iStr = 'days' + str(i)

    # Add total
    cursor.execute("""INSERT INTO total(report, days, totalTime, totalOffTime, totalSessionTime,
                        totalStandbyTime, longSessions, shortSessions, totalIdleTime, medianSessionLength,
                        uniqueUsers) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",(
            reportId,
            i,
            default(data[iStr]['total'].get('totalTime', 0), 0),
            default(data[iStr]['total'].get('totalOffTime', 0), 0),
            default(data[iStr]['total'].get('totalSessionTime', 0), 0),
            default(data[iStr]['total'].get('totalStandbyTime', 0), 0),
            default(data[iStr]['total'].get('longSessions', 0), 0),
            default(data[iStr]['total'].get('shortSessions', 0), 0),
            default(data[iStr]['total'].get('totalIdleTime', 0), 0),
            default(data[iStr]['total'].get('medianSessionLength', 0), 0),
            default(data[iStr]['total'].get('uniqueUsers', 0), 0),
        ))

    # Add Per location
    for location in data[iStr]['perLocation']:
        try:
            cursor.execute("""INSERT INTO perLocation(report, days, locationname, totalTime, totalOffTime,
                                totalSessionTime, totalStandbyTime, longSessions, shortSessions, totalIdleTime, medianSessionLength)
                                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",(
                    reportId,
                    i,
                    location['locationname'],
                    location['totalTime'],
                    location['totalOffTime'],
                    location['totalSessionTime'],
                    location['totalStandbyTime'],
                    location['longSessions'],
                    location['shortSessions'],
                    location['totalIdleTime'],
                    location['medianSessionLength']
                ))
        except KeyError:
            pass

    # Add Per VM
    for vm in data[iStr]['perVM']:
        cursor.execute("""INSERT INTO perVM(report, days, vm, sessions)
                            VALUES (%s, %s, %s, %s)""",(
                reportId,
                i,
                vm['vm'],
                vm['sessions']
            ))

    # Add timeframe
    cursor.execute('INSERT INTO timeframe(report, days, tsFrom, tsTo) VALUES (%s, %s, FROM_UNIXTIME(%s), FROM_UNIXTIME(%s))',(
            reportId,
            i,
            data[iStr]['tsFrom'],
            data[iStr]['tsTo']
        ))

    # Add dozmode
    cursor.execute("""INSERT INTO dozmod(report, days, vms_total, vms_new, vms_updated, vms_valid, lectures_total,
                        lectures_new, lectures_updated, lectures_valid, users_total, users_organizations)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",(
            reportId,
            i,
            default(data[iStr]['dozmod']['vms']['total'], 0),
            default(data[iStr]['dozmod']['vms']['new'], 0),
            default(data[iStr]['dozmod']['vms']['updated'], 0),
            default(data[iStr]['dozmod']['vms']['valid'], 0),
            default(data[iStr]['dozmod']['lectures']['total'], 0),
            default(data[iStr]['dozmod']['lectures']['new'], 0),
            default(data[iStr]['dozmod']['lectures']['updated'], 0),
            default(data[iStr]['dozmod']['lectures']['valid'], 0),
            default(data[iStr]['dozmod']['users']['total'], 0),
            default(data[iStr]['dozmod']['users']['organizations'], 0)
        ))

    # Add machines
    for item in data[iStr]['machines']['location']:
        if item['location']:
            cursor.execute('INSERT INTO machine(report, days, property, value, count) VALUES (%s, %s, %s, %s, %s)',(
                    reportId,
                    i,
                    'location',
                    item['location'],
                    item['count']
                ))
    for item in data[iStr]['machines']['ram']:
        cursor.execute('INSERT INTO machine(report, days, property, value, count) VALUES (%s, %s, %s, %s, %s)',(
                reportId,
                i,
                'ram',
                item['gbram'],
                item['total']
            ))
    for item in data[iStr]['machines']['cpumodel']:
        cursor.execute('INSERT INTO machine(report, days, property, value, count) VALUES (%s, %s, %s, %s, %s)',(
                reportId,
                i,
                'cpumodel',
                item['cpumodel'],
                item['total']
            ))
    for item in data[iStr]['machines']['systemmodel']:
        cursor.execute('INSERT INTO machine(report, days, property, value, count) VALUES (%s, %s, %s, %s, %s)',(
                reportId,
                i,
                'systemmodel',
                item['systemmodel'],
                item['total']
            ))
    for item in data[iStr]['machines']['realcores']:
        cursor.execute('INSERT INTO machine(report, days, property, value, count) VALUES (%s, %s, %s, %s, %s)',(
                reportId,
                i,
                'realcores',
                item['realcores'],
                item['total']
            ))
    for item in data[iStr]['machines']['kvmstate']:
        cursor.execute('INSERT INTO machine(report, days, property, value, count) VALUES (%s, %s, %s, %s, %s)',(
                reportId,
                i,
                'kvmstate',
                item['kvmstate'],
                item['total']
            ))

db.commit()
db.close()

print('Import complete!')

exit(0)