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:

</table> **
Total No. of Entries in the IIS logs:**

LOGPARSER "SELECT count(*) as hits, sc-status, cs-uri-stem from 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 </td> </tr>

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

LOGPARSER "SELECT count(*) as hits from " -i:IISW3C -o:DataGrid -q:off </td> </tr> </table>

Dumping out entries based upon responses:



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


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

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

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


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


Client IP that requested most no. of times:



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


Searching for specific HTTP Response Code:



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


Counting the No. of file extensions requested:



LOGPARSER "SELECT count(*) as hits from  where cs-uri-stem like ‘%." -i:IISW3C -o:DataGrid -q:off </td> </tr> </table>


Dumping out details for a specific file type:



LOGPARSER "SELECT count(*) as hits, cs-uri-stem from where cs-uri-stem like ‘%.‘ GROUP BY cs-uri-stem order by hits desc" -i:IISW3C -o:DataGrid -q:off </td> </tr>
In the above command replace with a one that you are searching for like ‘”.asp”, “.aspx”, “.php” etc </td> </tr> </table>


NOTE: Replace  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