Sunday, July 28, 2013

Querying IIS Log File Information Easily

Microsoft has a command line tool called LogParser that can easily be used to execute SQL statements against log files. The tool is extremely handy when there is problem with a web service or site hosted by IIS.

For example to see if a rogue consumer of your web service is doing something silly like looping through and flooding it with calls you can query the most called web service by IP address by issuing:

.\LogParser.exe -i:W3C "
select
    count(*),    
    date as date,  
    cs-uri-stem,    
    c-ip     
from 
    \\servername\logs\current\* 
group by
    date,  
    cs-uri-stem,    
    c-ip   
having
   count(*) > 500000
" -rtp:-1
This will return a nicely formatted result like so:
COUNT(ALL *) date         cs-uri-stem                   c-ip    
------------ ----------   ----------------------------- ------------- 
510832       2013-07-11   /ExampleServices/Test.asmx    10.1.1.1  
535199       2013-07-11   /ExampleServices/Example.asmx 10.1.1.2 
Log parser is typically installed in: C:\Program Files (x86)\Log Parser 2.2