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 ...