Ask yourself this question: what if everything could be queried with SQL? Microsoft’s LogParser does just that. It lets you slice and dice a variety of log file types using a common SQL-like syntax. It’s an incredibly powerful concept, and the LogParser implementation doesn’t disappoint. This architecture diagram from the LogParser documentation explains it better than I could:

logparser_architecture

The excellent forensic IIS log exploration with LogParser article is a good starting point for sample LogParser IIS log queries. Note that I am summarizing just the SQL clauses; I typically output to the console, so the actual, complete commandline would be

logparser “(sql clause)” -rtp:-1

Top 10 items retrieved:

SELECT TOP 10 cs-uri-stem as Url, COUNT(cs-uri-stem) AS Hits FROM ex*.log GROUP BY cs-uri-stem ORDER BY Hits DESC

Top 10 slowest items:

SELECT TOP 10 cs-uri-stem AS Url, MIN(time-taken) as [Min], AVG(time-taken) AS [Avg], max(time-taken) AS [Max], count(time-taken) AS Hits FROM ex*.log WHERE time-taken < 120000 GROUP BY Url ORDER BY [Avg] DESC

All Unique Urls retrieved:

SELECT DISTINCT TO_LOWERCASE(cs-uri-stem) AS Url, Count(*) AS Hits FROM ex*.log WHERE sc-status=200 GROUP BY Url ORDER BY Url

HTTP errors per hour:

SELECT date, QUANTIZE(time, 3600) AS Hour, sc-status AS Status, COUNT(*) AS Errors FROM ex*.log WHERE (sc-status >= 400) GROUP BY date, hour, sc-status HAVING (Errors > 25) ORDER BY Errors DESC

HTTP errors ordered by Url and Status:

SELECT cs-uri-stem AS Url, sc-status AS Status, COUNT(*) AS Errors FROM ex*.log WHERE (sc-status >= 400) GROUP BY Url, Status ORDER BY Errors DESC

Win32 error codes by total and page:

SELECT cs-uri-stem AS Url, WIN32_ERROR_DESCRIPTION(sc-win32-status) AS Error, Count(*) AS Total FROM ex*.log WHERE (sc-win32-status > 0) GROUP BY Url, Error ORDER BY Total DESC

HTTP methods (GET, POST, etc) used per Url:

SELECT cs-uri-stem AS Url, cs-method AS Method, Count(*) AS Total FROM ex*.log WHERE (sc-status < 400 or sc-status >= 500) GROUP BY Url, Method ORDER BY Url, Method

Bytes sent from the server:

SELECT cs-uri-stem AS Url, Count(*) AS Hits, AVG(sc-bytes) AS Avg, Max(sc-bytes) AS Max, Min(sc-bytes) AS Min, Sum(sc-bytes) AS TotalBytes FROM ex*.log GROUP BY cs-uri-stem HAVING (Hits > 100) ORDER BY [Avg] DESC

Bytes sent from the client:

SELECT cs-uri-stem AS Url, Count(*) AS Hits, AVG(cs-bytes) AS Avg, Max(cs-bytes) AS Max, Min(cs-bytes) AS Min, Sum(cs-bytes) AS TotalBytes FROM ex*.log GROUP BY Url HAVING (Hits > 100) ORDER BY [Avg] DESC

Via codinghorror.com