path: root/import.py

#!/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)