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