Using Logparser with Perfmon logs

Posted on February 8th 2010 by Joel Deutscher

Logparser is an extremely useful tool for processing perfmon counters. It provides the ability to query text-based data such as log files, XML files and CSV files. This post will show you how to take a perfmon produced CSV file, and perform basic analysis on the results using logparser.

Let’s start with our extremely small example CSV file (rawdata.csv):

Time \\SERVER\Process(System)\% Processor Time \\SERVER\Process(Idle)\% Processor Time
02/08/2010 13:19:54.101 0.104231 374.8148
02/08/2010 13:20:09.092 0.208462 386.5929
02/08/2010 13:20:24.083 0.312693 376.1698
02/08/2010 13:20:39.074 0.312693 366.5806
02/08/2010 13:20:54.064 0.729617 368.6652
02/08/2010 13:21:09.055 0.729617 371.3752
02/08/2010 13:21:24.046 0.416924 366.7891
02/08/2010 13:21:39.037 0.521155 349.9036

Using Logparser, we can perform SQL queries on our dataset. Unfortunately, when processing a large number of collections, logparser likes to return the following error message.

Error: Log row too long

This is where relog comes in. Relog allows you to resample a log file, and then create a new log file that is based on specified counters, a time period, or a sampling interval. The following relog command will process our example CSV file and produce a new file with only the “\\SERVER\Process(System)\% Processor Time” and time columns.

relog rawdata.csv -f CSV -c "\\\SERVER\Process(System)\% Processor Time" -o relogged.csv

Now we have a file that can be processed by logparser, its time to query our dataset to extract the min, max, avg and std dev. Unfortunately, the standard deviation function is not included in LogParser by default. Fortunately, a friendly forum post provided some insight into a solution, and with some slight modifications, it can be used for resource utilisation stats from perfmon.

The following query will extract the minimum, maximum, average and standard deviation from the re-logged counter file. Note that COUNTER should be replaced by the perfmon collection that you are interested in, i.e. “\\Server\Process(System)\% Processor Time”

min(TO_REAL([ COUNTER ])) as Minimum,
max(TO_REAL([ COUNTER  ])) as Maximum,
avg(TO_REAL([ COUNTER  ])) AS Average,
SQRROOT ( SUB ( DIV ( MUL(1.0, SUM(SQR(TO_REAL([ COUNTER  ]))) ), COUNT(*)) , SQR(Average) ) ) AS StdDev
INTO output.xml
FROM relogged.csv
where TO_REAL([ COUNTER ]) is not null

This can be used with logparser (most likely after using relog to trim the file) using the following command:

logparser file:query.txt -o:xml -i:csv

Note that the above query must be written to the query.txt file for this to work. The resulting output.xml file will look something like this.



Of course, all of this could be performed in an excel spreadsheet. However the real power of logparser is when it’s used in batch processing of perfmon results. After all, who wants to open excel and look at every resource counter when you can automate the statistical analysis of your permon collections?

About the Author

Joel Deutscher is an experienced performance test consultant, passionate about continuous improvement. Joel works with Planit's Technical Testing Services as a Principal Consultant in Sydney, Australia. You can read more about Joel on LinkedIn.

1 Comment

  1. Mike says:

    There’s an undocumented registry key that will allow logparser to load long lines:

    You can do this if you want to skip the relog step.