Microsoft LogParser 2.2 Tutorial I

One of my favorite tools that I use all the time is logparser. This tool is well known among many sys admins, but not as well known in the development world. To quote from Microsoft "Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®." Basically, it is a tool that can query anything! Some things I have used it for are IIS, Log4Net, FileMon, Exchange, Event, and Akamai logs. I have also used it to create charts and CSV files from these files.

I wanted to start a tutorial that would give insight into this great tool and will show you how to use it. I will also show how you can call it from C# and powershell and some great libraries to use.

LogParser is a console based application but there are some people who created various UI's for it. You can write the sql directly or as I like to do it store it in a .sql file and call it like this:

LOGPARSER -i:IISW3C file:C:\MyLogParserFile.sql -o:DataGrid

There are tons of switches that can be added, and we will go through this in in further articles but note that -i stands for input. Although logparser can usually figure out the format, you should try and tell it what format the file is. In this case, I wanted it to know that the file is an IIS log file. The -o stands for output and I wanted it to show the output in a datagrid. If you leave the -o out it will display it in the default format which is in the console. You can try logparser -h and it will give you some more details.

I wanted to start off by showing some basic queries against IIS logs.

CountClients.sql

select count(*) from c:\MyIIS.log

Top10LongestTimeTakenPagesOnAverage.sql

SELECT
top 10 TO_LOWERCASE(cs-uri-stem),
cs-uri-query,
TO_LOCALTIME(TO_TIMESTAMP(date, time)) as timestamp,
sc-bytes,
time-taken

FROM
c:\MyIIS.Log

ORDER BY
time-taken DESC

Top25MostPopularPages.sql

SELECT
top 25 cs-uri-stem,
count(cs-uri-stem) As Hits
FROM c:\MyIIS.log

GROUP BY
cs-uri-stem

ORDER BY
 count(cs-uri-stem) DESC

CountUniqueClients.sql

SELECT count(distinct c-ip) from c:\MyIIS.log

Please let me know if you had any specifics you would like me to discuss.