Lizarding About With Log Parser Lizard6th August 2015
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.
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:
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
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
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.
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
--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
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