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