diff options
Diffstat (limited to 'import.py')
| -rw-r--r-- | import.py | 176 |
1 files changed, 176 insertions, 0 deletions
diff --git a/import.py b/import.py new file mode 100644 index 0000000..65bafa5 --- /dev/null +++ b/import.py @@ -0,0 +1,176 @@ +#!/usr/bin/env python3 + +import sys +import os +import json +import re +import pymysql + +with open(sys.argv[1], 'r') as f: + data = json.load(f) + +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('mysql.oebsrv.int.intranet', 'bwlehrpool', 'weav3Nu8ShaifaePh6aiqu6aetei7e', 'bwlehrpool') +cursor = db.cursor(pymysql.cursors.DictCursor) + +# 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, + data[iStr]["total"]["totalTime"], + data[iStr]["total"]["totalOffTime"], + data[iStr]["total"]["totalSessionTime"], + data[iStr]["total"]["totalStandbyTime"], + data[iStr]["total"]["longSessions"], + data[iStr]["total"]["shortSessions"], + data[iStr]["total"]["totalIdleTime"], + data[iStr]["total"]["medianSessionLength"], + data[iStr]["total"]["uniqueUsers"], + )) + + # Add Per location + for location in data[iStr]["perLocation"]: + 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"] + )) + + # 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, + data[iStr]["dozmod"]["vms"]["total"], + data[iStr]["dozmod"]["vms"]["new"], + data[iStr]["dozmod"]["vms"]["updated"], + data[iStr]["dozmod"]["vms"]["valid"], + data[iStr]["dozmod"]["lectures"]["total"], + data[iStr]["dozmod"]["lectures"]["new"], + data[iStr]["dozmod"]["lectures"]["updated"], + data[iStr]["dozmod"]["lectures"]["valid"], + data[iStr]["dozmod"]["users"]["total"], + data[iStr]["dozmod"]["users"]["organizations"] + )) + + # 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) |