stlog2sql.py #1

#!/usr/bin/env python3
# -*- encoding: UTF8 -*-
#
#

import P4
import sys
from collections import OrderedDict

def main(argv):
    database = sys.argv[1]
    logfile = sys.argv[2]

    eventtypes = {
                '0'   :  'CommandStart',
                '1'   :  'ComputeEnd',
                '2'   :  'CommandEnd',
                '3'   :  'AnyError',
                '4'   :  'Error',
                '5'   :  'FatalError',
                '6'   :  'Audit',
                '7'   :  'PerformanceUsage',
                '8'   :  'NetworkPerformance',
                '9'   :  'DatabasePerformance',
                '10'  :  'Logappend',
                '11'  :  'Trigger',
                '12'  :  'ServerEvent',
                '13'  :  'Obliterate',
                '14'  :  'NetworkEstimates',
                '15'  :  'Integrity',
                '16'  :  'Authentication',
    }
    eventmap = {
                '0'   :  'commands',
                '1'   :  'commands',
                '2'   :  'commands',
                '3'   :  'errors',
                '4'   :  'errors',
                '5'   :  'errors',
                '6'   :  'audit',
                '7'   :  'perf_usage',
                '8'   :  'perf_net',
                '9'   :  'perf_db',
                '10'  :  'logappend',
                '11'  :  'triggers',
                '12'  :  'events',
                '13'  :  'obliterate',
                '14'  :  'network',
                '15'  :  'integrity',
                '16'  :  'auth',
    }
    logschema = {
                'commands'     : '0',
                'errors'       : '3',
                'audit'        : '6',
                'perf_usage'   : '7',
                'perf_net'     : '8',
                'perf_db'      : '9',
                'logappend'    : '10',
                'triggers'     : '11',
                'event'        : '12',
                'obliterate'   : '13',
                'network'      : '14',
                'integrity'    : '15',
                'auth'         : '16',
    }

# Drop and Create DBs
    print("CREATE DATABASE IF NOT EXISTS " + database + ";")
    print("USE " + sys.argv[1] + ";")

    # Create tables based on log schema
    p4 = P4.P4()
    p4.connect()

    for s, r in logschema.items():
        logschema = OrderedDict()
        logschema = p4.run_logschema(r)
    # print logschema
    print("DROP TABLE IF EXISTS " + s + ";")
    sqlQuery = "CREATE TABLE " + s + "("
    for x in logschema:
        field = x['f_name']
        sqlQuery += field + " VARCHAR(255) NULL,"
        sqlQuery += "f_lognum INT NULL,f_logfile  VARCHAR(255) NULL,f_offset INT NULL);"
    print(sqlQuery)

    # Process log
    log = p4.run_logparse(logfile)
    print("# {0} entries".format(len(log)))
    for set in log:
        print('###  ' + eventmap[set['f_eventtype']] + '  ###')
        sqlFields = []
        sqlValues = []
        for f, v in set.items():
            # print (f, v)
            sqlFields.append(f)
            sqlValues.append('"' + v + '"')
        sqlSet = "INSERT INTO " + eventmap[set['f_eventtype']] + '(' + ','.join(sqlFields) \
                 + ') VALUES (' + ','.join(sqlValues)
        sqlSet+=");"
        print (sqlSet)

if __name__ == "__main__":
   main(sys.argv[1:])