#!/usr/bin/env python3
import sys
import os
import json
import re
import pymysql
import configparser
import datetime
def default(x, default):
return default if x is None else x
c = configparser.ConfigParser()
c.read('config.ini')
filename = os.path.basename(sys.argv[1])
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(host=c.get('db', 'host'), user=c.get('db', 'username'), password=c.get('db', 'password'), database=c.get('db', 'name'), charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
cursor = db.cursor()
# Check if ip is already in names table
cursor.execute('SELECT testserver FROM names WHERE ip = %s', (ip))
entry = cursor.fetchall()
if len(entry) > 0: # Already in table
if entry[0]['testserver'] == 1:
print('This is a Testserver. Ignoring!')
db.close()
exit(0)
else: # Add to database
cursor.execute('INSERT INTO names(ip, name, testserver) VALUES (%s, %s, 0)', (ip, ip))
# Load data and timestamps
with open(sys.argv[1], 'r') as f:
data = json.load(f)
date = re.search('^[0-9]{4}-[0-9]{2}-[0-9]{2}', filename).group(0)
date = datetime.datetime.fromtimestamp(int(data['days7']['tsTo'])).date()
# Add report to database
try:
cursor.execute('INSERT INTO reports(date,ip,version) VALUES(%s, %s, %s)',(
date,
ip,
data.get('version', 'Unknown')
))
except pymysql.err.IntegrityError:
print('Report is already in database!')
db.close()
exit(0)
cursor.execute('SELECT LAST_INSERT_ID() as id')
reportId = cursor.fetchone()['id']
# Add server information to database
if 'server' in data:
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
if data[iStr]['dozmod'].get('disabled', False) != True:
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']
))
# Add base systems
for item in data[iStr]['baseSystem']:
cursor.execute('INSERT INTO basesystem(report, days, system, count) VALUES (%s, %s, %s, %s)',(
reportId,
i,
item['system'],
item['count']
))
db.commit()
db.close()
print('Import complete!')
exit(0)
