Lizarding About With Log Parser Lizard
I've recently been playing about with Request Filtering which is an interesting add-on for IIS7 onwards. I'll blog about that soon, but I have had so much fun as a result of switching on Request Filtering I thought I'd write about some of the consequences first.
It allows you to set up rules, so certain requests are filtered out before they even get to your site, resulting in less load and a supposedly safer system. Lets say we want to filter out a URL or query string that contains the word "script" as shown in the link below.
http://www.dombat.co.uk/?search=foo<script>alert('attacked')</script>I won't go into the mechanics of Request Filtering yet, but lets just say that the filter would return a http 404
To the attacker, 404 just means not found but in the IIS logs we can discover the subcode. Anyone that has taken a look into an IIS log knows that they can be hell. They're long, text based and not really for human consumption.
Step Foward Log Parser Lizard
Microsoft released a command line tool way back in the mid 00's called Log Parser that used SQL style syntax to query text based log files. Log Lizard takes this a step further by adding a really simple GUI.
It's very cheap too. Download from here
What Logs Can It Read?
It can read text based log files such as IIS, HTTP, Event, SQL Error and lots more too. Some logs need a bit more effort on your part to configure the file (e.g SQL Logs) but once it works you are able to save your query for future use.
You can see form this image that there are actually loads of logs that it can use straight out of the box:
Example
SELECT sc-status As Status,sc-substatus AS SubStatus,COUNT(sc-substatus) As SubStatusCount,CASE sc-substatus
WHEN 1 THEN 'Site Not Found'
WHEN 2 THEN 'ISAPI or CGI restriction'
WHEN 3 THEN 'MIME type restriction'
WHEN 4 THEN 'No handler configured'
WHEN 9 THEN 'File attribute hidden'
WHEN 16 THEN 'DAV request sent to the static file handler'
WHEN 17 THEN ' Dynamic content mapped to the static file handler via a wildcard MIME mapping'
WHEN 20 THEN ' Too Many URL Segments'
--5-19 below are Request Filtering
WHEN 5 THEN 'URL Sequence Denied'
WHEN 6 THEN 'Verb Denied'
WHEN 7 THEN 'File extension denied'
WHEN 8 THEN 'Hidden Namespace'
WHEN 10 THEN 'Request Header Too Long'
WHEN 11 THEN 'URL Double Escaped'
WHEN 12 THEN 'URL Has High Bit Chars'
WHEN 13 THEN 'Content Length Too Large'
WHEN 14 THEN 'URL Too Long'
WHEN 15 THEN 'Query String Too Long'
WHEN 18 THEN 'Query String Sequence Denied'
WHEN 19 THEN 'Denied by Filtering Rule'
END AS SubStatusDescription
FROM #IISW3C# WHERE sc-status = 404 GROUP BY Status , SubStatus ORDER BY Status
Results
Here we can see the sub-statuses, the count and the description. A full list of sub-statuses and their description can be found here https://support.microsoft.com/en-us/kb/943891
Other Queries
The following is a list of queries that have been gathered together from various sources, some I have written, most have been written by others, but all are useful. Give them ago and start to love parsing logs.
Note
To get full use of the logs, ensure that all relevant fields are being saved into the log (e.g by default many fields are not saved). See here to configure logging https://technet.microsoft.com/en-us/library/cc771850%28v=ws.10%29.aspx
Bytes Per File Extension
MUL(PROPSUM(sc-bytes),100.0) AS PercentageOfBytes,
Div(Sum(sc-bytes),1024) as AmountOfMbBytes
FROM #IISW3C#
GROUP BY Extension
ORDER BY PercentageOfBytes DESC
Win32 Error Codes and Description
SELECT sc-win32-status As Win32-Status,
WIN32_ERROR_DESCRIPTION(sc-win32-status) as Description,
COUNT(*) AS Hits
FROM #IISW3C#
WHERE Win32-Status<>0
GROUP BY Win32-Status
ORDER BY Win32-Status ASC
Blocked File Extensions (404.7)
SELECT DISTINCT EXTRACT_EXTENSION( cs-uri-stem ) AS Extension
FROM #IISW3C#
WHERE sc-status = 404 and sc-substatus = 7 --file extension denied
ORDER BY Extension DESC
Files Accessed Over the Web (HTTP 200)
SELECT DISTINCT TO_LOWERCASE(cs-uri-stem) AS URL, Count(*) AS Hits
FROM #IISW3C#
WHERE sc-status=200
GROUP BY URL ORDER BY URL
Overused Scripts by Day
A script (e.g. js or aspx) that is highly overused may be suspicious.
SELECT TO_STRING(TO_TIMESTAMP(date, time), 'yyyy-MM-dd') AS Day,
cs-uri-stem, COUNT(*) AS Total
FROM #IISW3C#
WHERE (sc-status<400 or sc-status>=500) AND (TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%'
OR
TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%'
OR
TO_LOWERCASE(cs-uri-stem) LIKE '%.js%')
GROUP BY Day, cs-uri-stem
ORDER BY cs-uri-stem
High Number of Hits to Single Page by One IP
--possible evidence of attacks
SELECT DISTINCT date, cs-uri-stem, c-ip, Count(*) AS Hits
FROM #IISW3C#
GROUP BY date, c-ip, cs-uri-stem
HAVING Hits>50
ORDER BY Hits Desc
Excessive Errors
--This may be a sign of attack
SELECT date, QUANTIZE(time, 3600) AS hour, sc-status,sc-substatus, Count(*) AS Errors
FROM #IISW3C#
WHERE sc-status>=400
GROUP BY date, hour, sc-status,sc-substatus
HAVING Errors>25
ORDER BY Errors DESC
Excessive HTTP 500
--This may be a sign that SQL injection is being attempted or exploited.
SELECT cs-uri-query, Count(*) AS Total
FROM #IISW3C#
WHERE sc-status>=500
GROUP BY cs-uri-query
ORDER BY Total DESC
Non Standard UserAgents
--Possible attack by automated tools or other hacker tools
SELECT DISTINCT cs(User-Agent)
FROM #IISW3C#
WHERE TO_LOWERCASE(cs(User-Agent)) NOT LIKE '%mozilla%' AND TO_LOWERCASE(cs(User-Agent)) NOT LIKE '%opera%'
ORDER BY cs(User-Agent)
Bytes Sent To Client
SELECT cs-uri-stem, Count(*) as Hits, AVG(sc-bytes) AS Avg, Max(sc-bytes) AS Max, Min(sc-bytes) AS Min, Sum(sc-bytes) AS Total
FROM #IISW3C#
WHERE TO_LOWERCASE(cs-uri-stem) LIKE '%.asp%'
or
TO_LOWERCASE(cs-uri-stem) LIKE '%.exe%'
GROUP BY cs-uri-stem
ORDER BY cs-uri-stem
HTTP errors ordered by URL and Status
SELECT cs-uri-stem AS Url, sc-status AS Status, COUNT(*) AS Errors
FROM #IISW3C#
WHERE (sc-status >= 400)
GROUP BY Url, Status
ORDER BY Errors DESC Page Hits By IP Address
SELECT c-ip, count(c-ip) as requestcount
FROM #IISW3C#
WHERE cs-uri-stem like '%.aspx%'
GROUP BY c-ip
ORDER BY count(c-ip) desc
Requests By IP Address
SELECT c-ip, count(c-ip) as requestcount
FROM #IISW3C#
GROUP BY c-ip
ORDER BY count(c-ip) desc
Top Browsers
SELECT to_int(mul(100.0,PropCount(*))) as Percent, count(*) as TotalHits, cs(User-Agent) as Browser
FROM #IISW3C#
GROUP BY Browser
ORDER BY Totalhits desc
Top Images – Hits, Time & bandwidth
SELECT Top 100 StrCat(Extract_Path(TO_Lowercase(cs-uri-stem)),'/') AS RequestedPath,
Extract_filename(To_Lowercase(cs-uri-stem)) As RequestedFile, Count(*) AS Hits, Max(time-taken) As MaxTime, Avg(time-taken) As AvgTime, Max(sc-bytes) As BytesSent
FROM #IISW3C#
WHERE (Extract_Extension(To_Lowercase(cs-uri-stem)) IN 'gif';'jpg';'png';'svg')) AND (sc-status = 200)
GROUP BY To_Lowercase(cs-uri-stem)
ORDER BY BytesSent, Hits, MaxTime DESC
Broken Links on Sites That Refer To Your Site
SELECT DISTINCT cs(Referer) as Referer, cs-uri-stem as Url
FROM #IISW3C#
WHERE
cs(Referer) IS NOT NULL AND
sc-status = 404 AND
(sc-substatus IS NULL OR sc-substatus=0)
Part 2 of this article is here
Sources and Further Reading
Lots more queries
https://mlichtenberg.wordpress.com/2011/02/03/log-parser-rocks-more-than-50-examples/
Forensics
http://digital-forensics.sans.org/blog/2011/02/10/computer-forensics-howto-microsoft-log-parser