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
...
After a lot of looking, I ended up using what I listed in the question. I still don't think it is the most efficient, but it works and is readable.
WHERE [c-ip] NOT LIKE (10.10.%, 10.11.%)
Best Answer
Okay.
So let's break it up into it's smaller parts, based upon what data we want and that we're looking at.
That gives us the following as a base:
Based on our conditions, we have the following then:
Now if you do this you can see the problem; cs-method like '%USER' returns a sc-status of 331, so if we attempt to combine them, that just isn't going to work.
Status code 230 is 'user has logged in, proceed' - http://support.microsoft.com/kb/318380 - while 331 is just saying the username is okay.
So let's step back and actually look at a log for when a user logs in. The cs-username, since they're authenticated, will be populated with the name of the account they're using.
Which means this should be what we want:
Based on a scan of a file - http://media.jamesrskemp.com/articles/IIS6FTPSample.log.txt - that looks to be correct.
Does that help?
And looking at my post again, the key part that was missed was that the first helps "determine what user names were used to login, or attempt to login, to an FTP site." If you search for FTPUser3 in my sample log above, you'll see a 331 is returned by IIS, even though I didn't create the FTPUser3 account. Seemingly for security purposes, it seems IIS 6 FTP returns 331 either way.