Charting with LogParser

A picture worth 1000 words and this is true also when you have to analyze IIS logs to get some statistics on your site; as you already know, LogParser is extremely flexible and very useful in such situation and its chart output format is what it’s needed.The first thing you need is a copy of Office Web Components installed on the machine; if you don’t, LogParser will complain and return this error:

Error creating output format “chart”: This output format requires a licensed Microsoft Office Chart Web Component to be installed on the local machine

From LogParser help:

chartType

Values:
name of chart type

Default:
Line

Description:
Chart type

Details:
The set of available chart types depends on the version of the Microsoft Office Web Components installed on the local computer.
For a list of the available chart types, type the following help command from the command-line shell: 

     LogParser -h -o:CHART

Currently the latest Office Web Components version available (the newest I’ve been able to find on the Internet) is version 11 which has been released for Office 2003.

Changes in the 2007 Office System

OWC11
Description: OWC11 is removed from the 2007 Office system and now ships only with Microsoft Office Project 2007. The DataFinder functionality in OWC11 is also removed. This affects solutions that use the spreadsheet, chart, pivot table, and data source control functionality of OWC11 in a Web page or client program that requires installation of this ActiveX control on the user’s computer. The user experience varies depending upon the host container of the OWC11 ActiveX control.

For Web pages, users might see a broken icon or a notification that OWC is required. For client programs, users might receive a runtime error or other notification. In these situations, users download OWC11 from the Microsoft Download Center. The notification message generated by Office programs provides a link to the download location for OWC11. Neither Microsoft Office Excel 2007 nor SharePoint Designer 2007 now generate Web pages that use OWC.

Reason for change: New technologies such as Windows SharePoint Services 3.0 with Excel server side capabilities and the new charting component in the 2007 Office system replace some features of OWC11.

And if you have Office 2007 like me? I installed OWC11 anyway and everything is still working fine so you should be fine; just in case you need the download links, here they are: Office 2003 Add-in: Office Web Components and Microsoft Office 2003 Web Components Service Pack 1 (SP1) for the 2007 Microsoft Office System.

A few samples

Bytes per data type as exploded pie

LogParser 
    "SELECT TO_UPPERCASE(EXTRACT_EXTENSION(cs-uri-stem)) AS PageType, MUL(PROPSUM(sc-bytes),100.0) AS Bytes 
    INTO Pie.gif FROM *.log 
    GROUP BY PageType ORDER BY Bytes DESC" 
    -chartType:PieExploded -chartTitle:"Bytes per data type" -categories:off -o:chart -i:iisw3c

Calculating percentages per data type

logparser 
    "select extract_extension(cs-uri-stem) as Resource, mul(propcount(*),100.0) as ResourceHits 
    into percentage.jpg from *.log 
    group by Resource order by ResourceHits desc" 
    -chartType:PieExploded3D -chartTitle:"Percentages per data type" -categories:off -o:chart -i:iisw3c

.aspx page hits per minute

logparser 
    "select quantize(time, 60) as TimeGenerated, count(*) as Hits 
    into qnt.gif from *.log 
    where to_lowercase(extract_extension(cs-uri-stem))='aspx' group by TimeGenerated" 
    -i:iisw3c -o:chart -chartType:Line

Getting the number of unique visitors (actually the number of unique client IP addresses) is a bit more complex. We could try the following:

select date, count(distinct c-ip) into UniqueIPs.gif from *.log group by date” -i:iisw3c -o:chart -chartType:Line

but we get this error:

Error: Semantic Error: aggregate functions with DISTINCT arguments are not supported with GROUP BY clauses

The solution in this case is to split the query into two separate commands; for example you could create a .bat file (or script, or whatever method you prefer) and run the following:

@echo off 

logparser 
    "select distinct date, c-ip into dates.txt from *.log" -i:iisw3c -o:w3c

logparser 
    "select date, count(c-ip) as UniqueIPs into UniqueIPs.gif 
    from dates.txt group by date order by date" 
    -i:iisw3c -o:chart -chartType:SmoothLine -view:on

Advanced carting with configuration scripts

Using an external script file (JScript or VBScript) it is possible to have a deeper control over the produced chart; this is based on two global objects which expose methods and properties that can be used to modify parameters such as the chart colors, the chart fonts, and many other attributes. Those two global objects are instances of the chartSpace and chart objects of the Microsoft Office Web Components ChartSpace object model, and they are named “chartSpace” and “chart“; for more information on those objects you can have a look at the MSDN ChartSpace Object Model documentation.

Taken from the LogParser documentation, we can use the following sample script to add a caption to the previous UniqueIPs chant and make the background color transparent:

// Add a caption
chartSpace.HasChartSpaceTitle = true;
chartSpace.ChartSpaceTitle.Caption = "Generated by Log Parser 2.2";
chartSpace.ChartSpaceTitle.Font.Size = 8;
chartSpace.ChartSpaceTitle.Position = chartSpace.Constants.chTitlePositionBottom;

// Change the background color
chart.PlotArea.Interior.Color = chartSpace.Constants.chColorNone;

@echo off 

logparser 
    "select distinct date, c-ip into dates.txt from *.log" -i:iisw3c -o:w3c

logparser 
    "select date, count(c-ip) as UniqueIPs into UniqueIPs.gif 
    from dates.txt group by date order by date" 
    -i:iisw3c -o:chart -chartType:SmoothLine -config:MyScript.js -groupSize: 800x600 -view:on

From here you can experiment using the ChartSpace object model to further customize the appearance of your charts: the limit is your imagination ?

Carlo

Quote of the day:

Ability will never catch up with the demand for it. – Malcolm Forbe