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:
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: https://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: https://forums.iis.net/default.aspx?GroupID=51
KB Article on Log Parser: https://support.microsoft.com/kb/910447.
More on Log Parser by Rahul Soni: https://blogs.msdn.com/b/rahulso/archive/category/14624.aspx
Log Parser Examples: https://technet.microsoft.com/en-us/library/ee692659.aspx
Forensic Log Parsing with Microsoft’s Log Parser: https://www.symantec.com/connect/articles/forensic-log-parsing-microsofts-logparser