Analyzing IIS server logs
Wednesday, June 10, 2009 at 1:20PM
Captain Rtfb in IIS logparser, Tips and Tricks

Today I had to analyze a Microsoft's IIS web server log, trying to find some search engine referrers. I received log files from over the last 5 years and I was asked to filter out the keywords entered by customers for marketing purposes. These files aren't stable Apache logs at all! Now and then, the number of columns differs order changes so a simple AWK or GREP could not be used. That's weird! Lucky as I am, there are some headers in each log file, but should I write some scripts for ordering these info? No!...

Microsoft presents a tool for a analyzing the IIS log files and this tool is rather good. It accepts queries in SQL format! Log parser 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, 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. The world is your database with Log Parser.

Some examples (more at source):

• Top 20 URLs Hit
logparser "SELECT TOP 20 cs-uri-stem, COUNT(*) AS Hits INTO Analysis.csv from ex061023.log group by cs-uri-stem order by Hits DESC"

• Referrer Host Names directing traffic to this server with count of pages referred (summary)
logparser "SELECT ReferringHost, count(*) AS TotalReferrals, Min(cs(Referer)) AS ExampleRefererURL USING CASE EXTRACT_TOKEN(cs(Referer),2, '/') WHEN null THEN 'NoReferer' ELSE EXTRACT_TOKEN(cs(Referer),2, '/') END as ReferringHost into IISLOG_ANALYSIS_REFERER_HOSTS.CSV FROM ex061023.log group by ReferringHost order by count(*) DESC"

Article originally appeared on dutchtechies (
See website for complete article licensing information.