Useful LogParser Queries

If you deal with production servers and you don’t use LogParser, you should. It gives SQL-like abilities to query web server logs an other log types (like Event Logs). Coding Horror has a great article about the tool as well, and a link to a good article about forensic log parsing. Here are my most used web queries:

Find Pages with 500 errors
logparser “SELECT cs-uri-stem as Url, sc-status as code, COUNT(cs-uri-stem) AS Hits FROM C:\ProdLogFiles\ex*.log WHERE (sc-status >= 500) GROUP BY cs-uri-stem, code ORDER BY Hits DESC” -o:CSV >> C:\Data\ErrorPages.csv

Find 404 Requests
logparser “SELECT cs-uri-stem as Url, sc-status as code, COUNT(cs-uri-stem) AS Hits FROM C:\ProdLogFiles\ex*.log WHERE (sc-status = 404) GROUP BY cs-uri-stem, code ORDER BY Hits DESC” -o:CSV >> C:\Data\404Pages.csv

Find the Slowest Pages
logparser “SELECT TOP 100 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 C:\ProdLogFiles\ex*.log GROUP BY Url ORDER BY [Avg] DESC” -o:CSV >> C:\Data\SlowPages.csv