diff options
Diffstat (limited to 'import.py')
| -rw-r--r-- | import.py | 188 |
1 files changed, 188 insertions, 0 deletions
diff --git a/import.py b/import.py new file mode 100644 index 0000000..542637d --- /dev/null +++ b/import.py @@ -0,0 +1,188 @@ +#!/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) + + |