Initial import into git

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)