Enhancing Log Parser Reports with Charts

When you need quick analysis of your traffic logs you won’t find an better tool than Microsoft’s free Log Parser. With Log Parser you can read a variety of log files including the Registry and Windows event logs. It’s ease of use comes from using SQL queries against your log file. You can get your data even faster by using multiple log parser queries in a batch file.\r\n\r\nimage\r\n\r\nThe other day I was helping someone who needed some “top 10” data from their site’s log. Since I had these in my trusty batch file I could provide the text reports within seconds. However, I like to offer a little more pizzazz when possible so this time I decided use Log Parser’s native charting capability to output the results with some nice charts.  As the saying goes a picture is worth a thousand words.\r\n\r\nHere’s the query I used to create the chart above:\r\n

logparser.exe -i:iisw3c "select top 10 cs-uri-stem, count(*)  into top10requests.gif \r\nfrom <file> group by cs-uri-stem order by count(*) desc" \r\n-o:CHART -chartType:pieexploded3d -categories:off -chartTitle:"Top 10 Requests"

\r\n \r\n\r\n

\r\n

Installing Office Web Components

\r\nCharting is a native feature of Log Parser however there is a dependency for Office 2003 Add-in: Office Web Components. Depending on where you are running Log Parser the first time you try to output your query to a chart you may see this error message:\r\n\r\nError creating output format “CHART”: This output format requires a licensed Microsoft Office Chart Web Component to be installed on the local machine\r\n\r\n

If you didn’t see the error above then you’re all set but if you saw the error then it will be necessary to install the Office Web Components before you can start outputting charts. Once you’ve downloaded the file just accept the License Agreement and click Install.\r\n\r\nimage\r\n\r\nThe installation runs quickly. Click OK to close the window.\r\n\r\nimage\r\n\r\n \r\n

Example Log Parser Reports with Charts

\r\nNow you’re ready to start creating some colorful charts. The most useful parameters in my opinion are –chartType, –chartTitle, –categories, –values, and –legend. There are some 20+ chart types that you can choose from including:  Pie, PieExploded, PieExlpoded3D, LineStacked, Line3D, BarClustered, ColumnClustered, Smooothline. The default chart type is Line.  To see all the possible chart options run this simple command:\r\n\r\nLogParser -h -o:CHART\r\n\r\nTo take your charts to the highest level of customization you can use an external configuration script with Jscript or VBscript . Take a look at the MSDN ChartSpace Object Model documentation for more information.\r\n\r\nHere are a few different charts with various options.\r\n\r\nimage\r\n

logparser.exe -i:iisw3c "select top 10 cs-uri-stem, count(*)  into top10requests.gif \r\nfrom x.log group by cs-uri-stem order by count(*) desc" \r\n-o:CHART -chartType:pieexploded3d -categories:off -chartTitle:"Top 10 Requests"

\r\n \r\n\r\n

 \r\n\r\nimage\r\n

logparser.exe -i:iisw3c "select top 10 sc-status, count(*)  into top10errorcodes.gif \r\nfrom x.log group by sc-status having sc-status not in ('200') order by count(*) desc" \r\n-o:CHART -chartType:column3d -categories:on -values:on -chartTitle:"Top Status Codes"

\r\n \r\n\r\n

 \r\n\r\nimage\r\n

logparser.exe -i:iisw3c "select top 10 cs-uri-stem, count(*)  into top10_404.gif \r\nfrom x.log group by cs-uri-stem, sc-status having sc-status in ('404') order by count(*) desc" \r\n-o:CHART -chartType:BarClustered3D -values:on -categories:on -chartTitle:"Top 10 404 Status"

\r\n \r\n\r\n

image\r\n

logparser.exe -i:iisw3c "select quantize(time, 60) as TimeGenerated, count(*) as Hits into \r\nhitsperminute.gif from %1 group by TimeGenerated" -o:chart -chartType:Line –chartTitle:"Hits per Minute"

\r\n \r\n\r\n

 \r\n\r\n \r\n\r\nimage\r\n\r\n \r\n

logparser.exe -i:iisw3c "SELECT TOP 10 cs-uri-stem AS RequestedFile, COUNT(*) AS TotalHits, \r\nMAX(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime into slow.gif from x.log \r\nwhere EXTRACT_FILENAME(cs-uri-stem) not in('%begin%') GROUP BY cs-uri-stem ORDER BY MaxTime, TotalHits DESC" \r\n-o:CHART -chartType:barclustered3d -values:off -categories:on -chartTitle:"Top 10 Slowest Requests"

\r\n \r\n\r\n

\r\n

In Summary

\r\nMicrosoft’s Log Parser is a powerful tool for log file analysis. You can use it to analyze text files, csv files, Window’s event logs and even the Windows Registry.  You can make boring reports come alive with colorful charts.  There is a dependency on Office Web Components for charting to work but that is easily solved. Thanks for reading.

Peter Viola

Creative, customer focused, results oriented, Senior Web Systems Engineer who enjoys providing the highest level of customer service supporting complex Windows hosting solutions. MCITP, MCSA, MCTS

More Posts - Website