Log Parser Queries


Log Parser is one of the most powerful tools available for parsing IIS logs. It can effectively parse GB’s of data in effective time. Below is the download link:

Download Log Parser 2.2

These are the few Log parser queries using the command line interface. I have used  it most of the time. I have used DataGrid as the output format. More details on the input and the output format can be found here: http://technet.microsoft.com/en-us/scriptcenter/dd919274.aspx

Below are the different type of queries:

Search for total number of static files that were requested:

LOGPARSER "SELECT count(*) as hits, sc-status, cs-uri-stem from <Log File Path> where cs-uri-stem not like ‘%.axd’ and cs-uri-stem not like ‘%.ashx’ and cs-uri-stem not like ‘%.aspx’ and and cs-uri-stem not like ‘%.asmx’ and cs-uri-stem not like ‘%.asp’ and cs-uri-stem not like ‘%.dll’ and cs-uri-stem not like ‘%.exe’ group by sc-status, cs-uri-stem order by hits desc" -i:IISW3C -o:DataGrid -q:off

In the above query I am eliminating the dynamic files. We could add more dynamic files to the list above.


Total No. of Entries in the IIS logs:


LOGPARSER "SELECT count(*) as hits from <Log File Path>" -i:IISW3C -o:DataGrid -q:off


Dumping out entries based upon responses:


LOGPARSER "SELECT count(*) as hits, sc-status from <Log File Path> GROUP BY sc-status order by hits desc" -i:IISW3C -o:DataGrid -q:off

Adding the requested resource (cs-uri-stem) to the above query: 

LOGPARSER "SELECT count(*) as hits, sc-status, cs-uri-stem from <Log File Path> GROUP BY cs-uri-stem, sc-status order by hits desc" -i:IISW3C -o:DataGrid -q:off


Client IP that was logged against a specific cs-host the most:

LOGPARSER "SELECT count(*) as hits, c-ip, cs-host, sc-status from <Log File Path> where cs-host='<Host-Header>’ GROUP BY c-ip, cs-host, sc-status order by hits desc" -i:IISW3C -o:DataGrid -q:off

 

Client IP that requested most no. of times:


LOGPARSER "SELECT count(*) as hits, c-ip, cs-host, sc-status from <Log File Path> GROUP BY c-ip, cs-host, sc-status order by hits desc" -i:IISW3C -o:DataGrid -q:off

  

Searching for specific HTTP Response Code:


LOGPARSER "SELECT count(*) as hits, sc-status, cs-uri-stem  from <Log File Path> where sc-status=404 GROUP BY cs-uri-stem, sc-status order by hits desc" -i:IISW3C -o:DataGrid -q:off

Counting the No. of file extensions requested:


LOGPARSER "SELECT count(*) as hits from <Log File Path>  where cs-uri-stem like ‘%.<file-extensions>" -i:IISW3C -o:DataGrid -q:off

Dumping out details for a specific file type:


LOGPARSER "SELECT count(*) as hits, cs-uri-stem from <Log File Path> where cs-uri-stem like ‘%.<file-extensions>‘ GROUP BY cs-uri-stem order by hits desc" -i:IISW3C -o:DataGrid -q:off

In the above command replace <file-extensions> with a one that you are searching for like ‘”.asp”, “.aspx”, “.php” etc

  

NOTE: Replace <Log File Path> with the location where the log files are store. Assuming they are stored at location: C:\Logs. Here is one e.g.:

LOGPARSER "SELECT count(*) as hits from C:\Logs\ex101003" -i:IISW3C -o:DataGrid -q:off

Alternatively, you can run this query on all the files within the folder using a wild-card:

LOGPARSER "SELECT count(*) as hits from C:\Logs\ex*" -i:IISW3C -o:DataGrid -q:off

As you can see we can write more flexible queries to extract further information.

I will be publishing more in future when I get time.

More Information:

Log Parser Forum: http://forums.iis.net/default.aspx?GroupID=51 

KB Article on Log Parser: http://support.microsoft.com/kb/910447.

More on Log Parser by Rahul Soni: http://blogs.msdn.com/b/rahulso/archive/category/14624.aspx

Log Parser Examples: http://technet.microsoft.com/en-us/library/ee692659.aspx

Forensic Log Parsing with Microsoft’s Log Parser: http://www.symantec.com/connect/articles/forensic-log-parsing-microsofts-logparser


Comments (1)

  1. ap says:

    Chote nice article..very helpful.