Tuesday, December 13, 2005

Log Parser

I came across a great article about Log parser from Exchange & Outlook Administrator (Windows IT Pro). It described the log parser tool and how to use it on a variety of logs.
The first thing I thought about was gathering statistics about OWA useage. I used the following query that I got from Technet:
logparser "SELECT TO_STRING(time, 'HH') AS Hour, COUNT(*) AS Hits INTO MyChart.jpg FROM ex*.log GROUP BY Hour ORDER BY Hour ASC" -i:IISW3C -o:CHART -chartType:ColumnClustered -chartTitle:"Hourly Hits" -groupSize:420x280
This outputs a great JPEG that shows you hits per hour.

I went to the Logpaerser forum to try and get some help in modifying the query so that I could get unique logons per hour. Here is what Daniel Einspanjer helped me develop Forum Link:

First, create the two sql files:
QAuthenticationv2(1).sql
SELECT
TO_STRING(time, 'HH') AS Hour,
COUNT(*) AS Hits,
cs-username
INTO STDOUT
FROM c:\owa_logs\*
WHERE
(cs-username IS NOT NULL)
AND
(sc-status = 200)
GROUP BY Hour, cs-username

QAuthenticationv2(2).sql
SELECT
Hour,
SUM(Hits) AS Hits
INTO Authenticationv2.gif
FROM STDIN
GROUP BY Hour

Then execute them as follows:
c:\logparser>Logparser.exe file:QAuthenticationv2(1).sql -i:IISW3C -o:CSV | LogParser.exe -i:CSV file:QAuthenticationv2(2).sql -o:CHART -charttype:Columnclustered

Links:
TechNet
Logparser Download
http://www.logparser.com/
Log Parser Book