jtltodb.py

#!/usr/bin/python

"""
Description : Split JTL file into a comma delimited CVS by : Oliver Erlewein (c)2008 Date : 04.02.2008 Lang : Python 2.7+
Load data from csv to oracle : Eugene Kazakov (c) 2012-06
XML Log format 2.1
The sample attributes have the following meaning:
Attribute  Content
by         Bytes
de         Data encoding
dt         Data type
ec         Error count (0 or 1, unless multiple samples are aggregated)
hn         Hostname where the sample was generated
it         Idle Time = time not spent sampling (milliseconds) (generally 0)
lb         Label
lt         Latency = time to initial response (milliseconds) - not all samplers support this
na         Number of active threads for all thread groups
ng         Number of active threads in this group
rc         Response Code (e.g. 200)
rm         Response Message (e.g. OK)
s          Success flag (true/false)
sc         Sample count (1, unless multiple samples are aggregated)
t          Elapsed time (milliseconds)
tn         Thread Name
ts         timeStamp (milliseconds since midnight Jan 1, 1970 UTC)
"""
import getopt
import sys
import re
import time
import os
import datetime
from subprocess import Popen, PIPE


startTime = time.time()
cnt = 0
cnt2 = 0
failCnt = 0
reCompile = re.compile("\s([^\s]*?)=\"(.*?)\"")
delimiterCharacterOut = ","

def usage():
    usageRules = """Usage:
    """+os.path.basename(__file__)+"""
    --jtl=JmeterLog.jtl                          jmeter log file
    --csv=csvFile.csv                            output csv file
    --filterString=^<                            regexp string for filtering
    --connString=user/pass@host:port/scheme      connection string for oracle
    --project==projectName                       name of project
    --environment=server                         environment description
    --duration=1/2/3 h/d/m                       duration of test
    --test_comment='this great test happened
    with us in beautifull summer day etc'        test description
    --start_date=1970-01-01                      date when test started
    --accepted=yes/no                           results of test
    -h                                          print this usage
    """
    print usageRules

def writeCSVLine(line):

    x = reCompile.findall(line)
    a = dict((row[0], row[1]) for row in x)
    try:
        #shortTS = str(int(int(a['ts'])/1000))
        #x = str(datetime.datetime.fromtimestamp(float(shortTS)))[0:19]
        #print int(a['ts'])/1000
    #print time.strftime("%Y-%m-%d %H:%M:%S.", time.localtime(int(a['ts'])/1000)) + a['ts'][-3:]
        b = time.strftime("%Y-%m-%d %H:%M:%S.", time.localtime(int(a['ts'])/1000)) + a['ts'][-3:] + "," + a['t'] + "," + a['lt'] + "," + a['s'] + ",\""\
        + a['lb'] + "\"," + a['rc'] + "," + a['rm'] + ",\"" + a['tn'] + "\","\
        + a['dt'] + "," + a['by'] + "\n"
    except:
        return -1
    csvFile.write(b)
    return 1

if __name__ == "__main__":
 try:
  # Get option from command line
  opts, args = getopt.getopt(sys.argv[1:], 'h', ['jtl=', 'csv=', 'filterString=',
                                                 'connString=', 'project=',
                                                 'environment=', 'duration=',
                                                 'test_comment=',
                                                 'start_date=',
                                                  'accepted='])
 except getopt.GetoptError as err:
  # print help information and exit:
  print str(err) # will print something like "option -a not recognized"
  usage()
  print err
  sys.exit(2)

#options processing
for o, v in opts:
    if o == '-h':
        usage()
        sys.exit()
    elif o == '--jtl':
        jtlInfile = v
    elif o == '--csv':
        cvsOutfile = v
    elif o == '--filterString':
        reFilter = v
    elif o == '--connString':
        connString = v
    elif o == '--project':
        project = v
    elif o == '--environment':
        environment = v
    elif o == '--duration':
        duration = v
    elif o == '--test_comment':
        test_comment = v
    elif o == '--start_date':
        start_date = v
    elif o == '--accepted':
        accepted = v

try:
    jtlFile = open(jtlInfile, "r")
    csvFile = open(cvsOutfile, "w")
except:
    raise
#parse jtl file to csv format
print "Splitting JTL file"
print "Filtering on regular expression : " + reFilter
cmpFilter = re.compile(reFilter)

for line in jtlFile:
    try:
        if cmpFilter.search(line):
#            print line
            returnVal = writeCSVLine(line)
            if returnVal < 0:
                failCnt += 1
            else:
                cnt2 += 1
    except:
        print 'Error in line : ', cnt, line
        raise
    cnt += 1

endTime = time.time()
print "Time taken : ", str(endTime-startTime)
print "Lines processed : ", cnt
print "Lines that passed the filter : ", cnt2
print "Lines skipped (error?) : ", failCnt

jtlFile.close()
csvFile.close()


#get next test_id from sequence
proc = Popen(['sqlplus', '-S', connString],
              stdin=PIPE, stdout=PIPE, stderr=PIPE)
proc.stdin.write("select test_id_seq.nextval from dual;\n")
stdout, stderr = proc.communicate()
test_id = re.search("\d+", stdout)

#rewrite control file with new test_id value
try:
    ctl = open("jtlToCSV.ctl", "w")
except:
 raise

ctlString = """\
LOAD DATA
        INFILE  '"""+cvsOutfile+"""'
        APPEND
INTO TABLE results
        FIELDS
        TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        trailing nullcols
( test_id constant """ + test_id.group(0) + """,
time_stamp timestamp 'YYYY-MM-DD HH24:mi:ss.FF',
elapsed,
latency,
success,
label,
responsecode,
responsemessage,
threadname,
datatype,
bytes
)
"""
ctl.write(ctlString)
ctl.close()

#add description about test into "tests" table
proc = Popen(['sqlplus', '-S', connString],
              stdin=PIPE, stdout=PIPE, stderr=PIPE)
proc.stdin.write("insert into tests values ('" + test_id.group(0) + "', '"+project+
                 "', '"+environment+"', '"+duration+"', '"+test_comment+"', '"
                 +start_date+"', '"+accepted+"');\n")
stdout, stderr = proc.communicate()
print "Insert in test table:"+stdout

#load data from csv to "results" table with sql loader
proc = Popen(['sqlldr', connString, 'control=jtltocsv.ctl'],
              stdin=PIPE, stdout=PIPE, stderr=PIPE)
stdout, stderr = proc.communicate()
print stdout