Using Logparser with Perfmon logs

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):

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”

SELECT
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.

 
 
 
 
 
 
 
]>

 
  
  0.104231
  
  
  0.729617
  
  
  0.416924
  
  
  0.214878
  
 

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?