Merge remote-tracking branch 'import/master'

3 files changed, 190 insertions, 0 deletions

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..2fa7ce7
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1 @@
+config.ini
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)
+
+
diff --git a/requirements.txt b/requirements.txt
new file mode 100644
index 0000000..55aa7d0
--- /dev/null
+++ b/requirements.txt
@@ -0,0 +1 @@
+PyMySQL==0.9.3