summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorLukas Metzger2020-06-10 20:40:43 +0200
committerLukas Metzger2020-06-10 20:40:43 +0200
commitb27c818922ee21ebf5b66e388432bb8d1a708a91 (patch)
tree6b2cac69690d01623f34083ac82fba47617a6763
parentInitial commit (diff)
parentAdded python requirements file (diff)
downloadbwlp-statistics-b27c818922ee21ebf5b66e388432bb8d1a708a91.tar.gz
bwlp-statistics-b27c818922ee21ebf5b66e388432bb8d1a708a91.tar.xz
bwlp-statistics-b27c818922ee21ebf5b66e388432bb8d1a708a91.zip
Merge remote-tracking branch 'import/master'
-rw-r--r--.gitignore1
-rw-r--r--import.py188
-rw-r--r--requirements.txt1
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