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
Comments
Anonymous
May 18, 2011
Chote nice article..very helpful.Anonymous
September 14, 2016
Im trying to parse an IIS log file using LogParser 2.2, however i'm having a problem when it comes to the user agent because it sees the spaces in the following user agent and thinks it's a new field..."Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.2 (KHTML, like Gecko) Chrome/15.0.874.121 Safari/535.2"And it doesn't care about the "" around the string, anyone know of anyway to make it treat anything in between the "" as one field?thanks,p.s. This is from an log generated by the IIS Advanced Logging module.