Word of Mike - Bit of Fun with Log Parser

Hi, welcome to Word of Mike, my little corner of the internet. I am a Software/Web Developer working in North Yorkshire. I mainly write about programming but my other passion is politics so beware. (click to hide)

2012-07-12 17:10:59 UTC

Bit of Fun with Log Parser

I was tasked with coming up with a way of creating graphs based on CSV log files from a telephone system, after a bit of research I came across the peach that is Log Parser. This is a little explanation of some basic usage of Log Parser to SQL query CSV files directly and produce graphs as an output.

The log files I wanted to query were being generated nightly by a telephone system, and included information on every single call, incoming and outgoing. I wanted to pull the number of incoming calls over a particular period and display this information by day of the week.

Log Parser is pretty simple to use command line program, you can specify from a number of different input and output formats, and then write SQL queries (using a subset of SQL functions and clauses). For my example, I am using CSV files as input and I am outputting to a bar chart.

My log files did not have headers, so my first job was to create a header file so I could reference columns in my query. This is simply a one line comma separated text file with the headers. You specify this header file with the flags

-headerRow OFF -iHeaderFile C:\logs\header\header.txt

I wrote the following SQL query to count the number of incoming calls in the previous seven days and group them by day of the week:

SELECT to_string(to_timestamp(c1, 'yyyy/MM/dd HH:mm'), 'dddd') as Day, count(*) as Calls
INTO graph.gif
FROM C:\logs\*.txt
WHERE c5 = 'I'
    AND to_timestamp(c1, 'yyyy/MM/dd HH:mm')
        BETWEEN
            sub(system_timestamp(), timestamp('07', 'dd'))
            AND system_timestamp()
GROUP BY Day

Let's start at the top, the messy looking string of functions simply turns my c1 column, which is a date and time, into a string representation of the day of the week, e.g. 'Monday'. I then set the file name of the graph I want to create and using FROM I specify where my log files are, note that I use a wildcard to make Log Parser parse every txt file in that folder.

To put this into LogParser is simple enough. We need to set the input type to CSV:

-i:CSV

, the output type to chart:

-o:CHART

, specify the chart type:

-chartType:ColumnStacked

and add the header information as already mentioned.

"C:\Program Files (x86)\Log Parser 2.2\Logparser.exe" -i:CSV "SELECT to_string(to_timestamp(c1, 'yyyy/MM/dd HH:mm'), 'dddd') as Day, count(*) as Calls INTO graph.gif FROM C:\logs\*.txt WHERE c5 = 'I' AND to_timestamp(c1, 'yyyy/MM/dd HH:mm') BETWEEN SUB(system_timestamp(), timestamp('07', 'dd')) AND system_timestamp() GROUP BY Day" -o:CHART -chartType:ColumnStacked -headerRow OFF -iHeaderFile C:\logs\header\header.txt

And the resulting graph:

Log Parser Chart

This is the basic query, but LogParser has vastly more options we can play with to make the output look a little nicer. We can also use configuration scripts to do more complex things with our graph. I will go into those in a follow-up post soon.

@mikecmpbll