Log Parser Lizard

Log Parser Lizard is a great free tool if you use Log Parser to parse IIS logs using SQL. It’s a visual tool to query the logs. It also comes with pre-made queries. Let’s take a look at one, “Requests and Full Status by Number of Hits” in IIS logs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Let's query the IIS W3SVC80086301 Log file c:\temp\logs\ex080918.log
SELECT 	STRCAT(	cs-uri-stem, 
		REPLACE_IF_NOT_NULL(cs-uri-query, STRCAT('?',cs-uri-query))
		) AS Request, 
	STRCAT(	TO_STRING(sc-status), 		
		STRCAT(	'.',
			COALESCE(TO_STRING(sc-substatus), '?' )
			)
		) AS Status, 
	COUNT(*) AS Total 
FROM c:\temp\logs\ex080918.log 
WHERE (sc-status >= 400) 
GROUP BY Request, Status 
ORDER BY Total DESC

Which gives you the following result (depending, of course, what’s in your logs):

2

Also, I could’ve queried all the log files put together, such as:

1
select * from c:\temp\logs\*log

Also, you can create global variables and use them in your queries so that you don’t always have to put the full path to a file. For example:

3

I’m setting the variable IISW3C equal to c:\temp\logs\ex*.log . The queries that come with this tool use these variables (keys) as a shortcut. For your IIS logs dir, you may want to set it up to point to C:\WINDOWS\system32\LogFiles\W3SVC80086301 . Once you’ve done this, you can do (HIT F5 to run query):

1
2
-- Get the top 10 from all IIS logs
select top 10 * from #IISW3C#

You can also view LogParser graphs from this tool. Let’s try the query to show all extension with total hits:

1
2
3
4
5
6
7
SELECT  TO_UPPERCASE(EXTRACT_EXTENSION( cs-uri-stem )) AS Extension, 
	COUNT(*) AS [Total Hits]
FROM #IISW3C# 
GROUP BY Extension 
-- Ignore .CFM extension
HAVING TO_UPPERCASE(EXTRACT_EXTENSION( cs-uri-stem )) <> 'CFM'
ORDER BY [Total Hits] DESC

4s

Leave a Reply