Connect with us

Hi, what are you looking for?

IIS

Using Microsoft Log Parser to query huge log files and only display the results you need

Have you ever had a giant log file or CSV that you needed to go through and pull results from quickly?  Sure you can try dumping it into Excel and trying different filters and sort orders but that’s a waste of time.  It’s much faster to pull your data via a query like in a database.  Microsoft has a tool called Log Parser that does just that.  You can use queries to parse any kind of text based file.

You can download Log Parser 2.2 from Microsoft here:  http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

Just install it and try it out by opening up a command prompt, navigating to your install path, and running the logparser executable.  It will display a list of commands to get you familiar with it.   I first started using it to parse huge IIS logs.  It’s pretty easy to use, here’s an example of pulling the top 10 pages hit on your site:

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

or all the Error 500s for a particular site:

logparser "SELECT [cs-uri-stem], [cs-uri-query], Count(*) AS [Hits] FROM c:\logs\ex*.log WHERE sc-status = 500 GROUP BY [cs-uri-stem], [cs-uri-query] order by [hits], [cs-uri-stem] DESC" -rtp:-1 -i:iisw3c

You can even throw the above in a batch file that schedule to run every hour and do something like:

All5005Errors.bat > All500Errors.txt

to log it all to disk.  Or even easier, use INTO in your SQL syntax to dump to a file like a .csv so it reads like:

logparser "SELECT [cs-uri-stem], [cs-uri-query], Count(*) AS [Hits] INTO All500Errors.csv FROM c:\logs\ex*.log WHERE sc-status = 500 GROUP BY [cs-uri-stem], [cs-uri-query] order by [hits], [cs-uri-stem] DESC" -rtp:-1 -i:iisw3c

There’s tons and tons of nice little queries people have written, for example I’ve personally used some from Jeff Atwood’s site here:  http://www.codinghorror.com/blog/archives/000369.html

Or you can got to the IIS.NET forums where there is an entire forum and many sub-forums dedicated to Log Parser here:  http://forums.iis.net/default.aspx?GroupID=51

Another cool tool over at CodePlex…Visual Log Parser:  http://www.codeplex.com/visuallogparser

I actually haven’t used this yet but it is out there if you get bored of using command line.  LMK if you guys decide to try it out.

1 Comment

1 Comment

  1. Patrick

    February 9, 2010 at 11:11 AM

    Thanks for good pointers in this article. There is also another script that you can resource for parsing web server log files – http://www.biterscripting.com/SS_WebLogParser.html . Check it out when you get a chance. I have had good experience with it.

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like

Apache

Today I would like to go over proper URL redirection when using SSL but first I would like to preface this by describing what...

Citrix Workspace

You can use FIDO2 hardware security keys plugged into your physical desktop over the Citrix HDX remoting protocol for use with virtualized Windows Desktop...

Exchange 2003

A useful Exchange 2003 guide I wrote for a friend’s blog originally but I am posting it here on mine now for your viewing...

Apache

In a worst case scenario and all your web servers have failed, what do you do? You could have a standby group of servers...

JasonSamuel.com began in 2008 as a way for me to give back to the IT community. This website features the latest news and how-to's on enterprise mobility, security, virtualization, cloud architecture, and other technologies I work with. This website has evolved over time to become a go-to reference hub for these technologies. It receives hundreds of thousands of unique visitors from all over the world each month. More details on the About Me page.
Copyright © 2008-2023 JasonSamuel.com