Log Parser – Querying IIS Logs

Recently ​I needed to get statistics about a web site running on the production server. I found the following Microsoft software easy to use and it gave me the results I needed really quickly.

Background

Log Parser 2.2 is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, IIS logs and Active Directory. You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart. Most software is designed to accomplish a limited number of specific tasks. Log Parser is different… the number of ways it can be used is limited only by the needs and imagination of the user. The world is your database with Log Parser.

How To Use It

I copied the date range of logs I needed from production to my laptop in order to run the query against them as I was querying 1 years worth of data. You can run the UI against the live logs as it doesn’t lock the file, but I wanted to do this in isolation from the production server.

I was able to run the following query against the logs for 2017 to give me stats around how many people had accessed the application on a daily basis for 2017.

  • Open log parser
  • Point it at the log files from the relevant environment
  • Type your sql syntax and press execute

Example SQL syntax

select cs-uri-stem, cs-username , count(cs-uri-stem), TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) AS [LocalDate] from '[LogFilePath]' where cs-uri-stem = '/CMS/MainFrameset.aspx' group by cs-uri-stem, cs-username, TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) order by TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))), count(cs-uri-stem) desc

 

Download Log Parser 2.2 (command line)

http://www.microsoft.com/en-gb/download/details.aspx?id=24659

Download Log Parser Studio (UI)

https://gallery.technet.microsoft.com/office/Log-Parser-Studio-cd458765

50 Examples to get you going

https://mlichtenberg.wordpress.com/2011/02/03/log-parser-rocks-more-than-50-examples/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s