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:
"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:
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.