Iis – LogParser query to grab only external IP addresses from IIS logs

A good indicator for hacking activies or other attacks is the number of errors per hour. The following script returns the dates and hours that had more than 25 error codes returned. Adjust the value depending on the amount of traffic on the site (and the quality of your web application ;-) ).

SELECT date as Date, QUANTIZE(time, 3600) AS Hour, 
       sc-status as Status, count(*) AS ErrorCount
FROM   {filename} 
WHERE  sc-status >= 400 
GROUP BY date, hour, sc-status 
HAVING ErrorCount > 25
ORDER BY ErrorCount DESC

The result could something like this:

Date       Hour     Status ErrorCount
---------- -------- ------ ------
2009-07-24 18:00:00 404    187
2009-07-17 13:00:00 500    99
2009-07-21 21:00:00 404    80
2009-07-03 04:00:00 404    45
...

The next query detects an unusually high number of hits on a single URL from one IP address. In this example I chose 500, but you may have to change the query for edge cases (excluding the IP address of Google London for example ;-) .)

SELECT DISTINCT date AS Date, cs-uri-stem AS URL,
      c-ip AS IPAddress, Count(*) AS Hits
FROM  {filename}
GROUP BY date, c-ip, cs-uri-stem
HAVING Hits > 500
ORDER BY Hits Desc

Date       URL                                 IPAddress       Hits
---------- ----------------------------------- --------------- ----
2009-07-24 /Login.aspx                         111.222.111.222 1889
2009-07-12 /AccountUpdate.aspx                 11.22.33.44     973
2009-07-19 /Login.aspx                         123.231.132.123 821
2009-07-21 /Admin.aspx                         44.55.66.77     571
...

Have a look here.