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:
-- 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):
Also, I could’ve queried all the log files put together, such as:
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:
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):
-- 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:
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
Categories