Log Parser – Transforming Plain Text Files

This post describes how to solve a specific problem with Microsoft’s Log Parser tool.  For background on the tool (and lots of examples), start here.

The Problem

Given a file named MyLog.log that looks like this…

ip=0.0.0.0 date=20160620 time=06:00:00 device=A23456789 log=00013
ip=0.0.0.1 date=20160621 time=06:00:01 device=A13456789 log=00014
ip=0.0.0.2 date=20160622 time=06:00:02 device=A12456789 log=00015
ip=0.0.0.3 date=20160623 time=06:00:03 device=A12356789 log=00016
ip=0.0.0.4 date=20160624 time=06:00:04 device=A12346789 log=00017
ip=0.0.0.5 date=20160625 time=06:00:05 device=A12345789 log=00018
ip=0.0.0.6 date=20160626 time=06:00:06 device=A12345689 log=00019
ip=0.0.0.7 date=20160627 time=06:00:07 device=A12345679 log=00020
ip=0.0.0.8 date=20160628 time=06:00:08 device=A12345678 log=00021
ip=0.0.0.9 date=20160629 time=06:00:09 device=A123456789 log=00022

…transform it into a tab-separated file with a header row.  Each field should include only the field value (and not the field name).

Notice that the original file has no header, the fields are separated with spaces, and the field name is part of each field (i.e. "ip=").

The Solution

Step 1)

logparser -i:TSV -iSeparator:space -headerRow:OFF
     "select * into ‘MyLogTemp.log’ from ‘MyLog.log’"
     -o:TSV -oSeparator:space -headers:ON

In this command, -i:TSV -iSeparator:space informs Log Parser that the input file is a space-separated text file, and -headerRow:OFF lets Log Parser know that the file has no headers.  Likewise, -o:TSV -oSeparator:space -headers:ON tells Log Parser to output a space-separated text file with headers.

This produces a file named MyLogTemp.log with the following content:

Filename RowNumber Field1 Field2 Field3 Field4 Field5
MyLog.log 1 ip=0.0.0.0 date=20160620 time=06:00:00 device=A23456789 log=00013
MyLog.log 2 ip=0.0.0.1 date=20160621 time=06:00:01 device=A13456789 log=00014
MyLog.log 3 ip=0.0.0.2 date=20160622 time=06:00:02 device=A12456789 log=00015
MyLog.log 4 ip=0.0.0.3 date=20160623 time=06:00:03 device=A12356789 log=00016
MyLog.log 5 ip=0.0.0.4 date=20160624 time=06:00:04 device=A12346789 log=00017
MyLog.log 6 ip=0.0.0.5 date=20160625 time=06:00:05 device=A12345789 log=00018
MyLog.log 7 ip=0.0.0.6 date=20160626 time=06:00:06 device=A12345689 log=00019
MyLog.log 8 ip=0.0.0.7 date=20160627 time=06:00:07 device=A12345679 log=00020
MyLog.log 9 ip=0.0.0.8 date=20160628 time=06:00:08 device=A12345678 log=00021
MyLog.log 10 ip=0.0.0.9 date=20160629 time=06:00:09 device=A123456789 log=00022

This hasn’t done much.  In fact is has added some stuff that is not relevant (the Filename and RowNumber columns), while leaving field names in each fields and maintaining the space field separator.  However, it HAS added headers (Field1, Field2, ect), which are needed for the second step.

Step 2)

logparser -i:TSV -iSeparator:space -headerRow:ON
     "select REPLACE_STR(Field1, ‘ip=’, ”) AS ip,
               REPLACE_STR(Field2, ‘date=’, ”) AS date,
               REPLACE_STR(Field3, ‘time=’, ”) AS time,
               REPLACE_STR(Field4, ‘device=’, ”) AS device,
               REPLACE_STR(Field5, ‘log=’, ”) AS log
     into ‘MyLogTransformed.log’
     from ‘MyLogTemp.log’"
     -o:TSV -oSeparator:tab -headers:ON

The input and output specifications in this command are similar to those in Step 1, except here the input file has headers (-headerRow:ON) and the output file is tab-separated (-oSeparator:tab) instead of space-separated.  The main difference is in the SELECT statement itself, where the use of the REPLACE_STR function removes the field names from the field values and the AS statement assigns the desired headers to each column of data.  Notice that the REPLACE_STR function uses the headers that were added in Step 1.

This produces the final result in a file named MyLogTransformed.log:

ip     date     time     device     log
0.0.0.0     20160620     06:00:00     A23456789     00013
0.0.0.1     20160621     06:00:01     A13456789     00014
0.0.0.2     20160622     06:00:02     A12456789     00015
0.0.0.3     20160623     06:00:03     A12356789     00016
0.0.0.4     20160624     06:00:04     A12346789     00017
0.0.0.5     20160625     06:00:05     A12345789     00018
0.0.0.6     20160626     06:00:06     A12345689     00019
0.0.0.7     20160627     06:00:07     A12345679     00020
0.0.0.8     20160628     06:00:08     A12345678     00021
0.0.0.9     20160629     06:00:09     A123456789     00022

More Information

See Log Parser’s built-in help for additional explanations of the Log Parser features used in the solution.  In particular, look at the following:

logparser -h
logparser -h -i:TSV
logparser -h -o:TSV
logparser -h FUNCTIONS REPLACE_STR

Advertisements

More Log Parser Resources

I’ve previously blogged about my favorite tool for IIS log analysis, Log Parser.

You can see my previous post here.  At the end of that post I list links to additional Log Parser discussion and examples.  Today I found a couple more resources that belong on that list:

  • This one, from the Nuttin but Exchange blog, is similar to what I had posted, but touches on some additional options and functions that I did not describe.
  • LogParserPlus.com is a web site devoted specifically to Log Parser.  It includes articles, examples, and comprehensive lists of Log Parser expressions and functions.

A GUI for Log Parser?

In a earlier post on this blog, I discussed the Log Parser tool from Microsoft, and provided an extensive list of examples showing how to use the tool.  I also wondered why Log Parser has never gained more traction among developers that use Microsoft’s tools.

Recently I received a comment on that blog entry.  The commenter praised the information I had provided, and then went on to mention a tool that provides a GUI for Log Parser.  (If you are not familiar with Log Parser, it is a command line tool for querying text-based data and Windows data sources such as the event log).

I thought the comment was nice, but wondered about the name-dropping of the GUI tool.  WordPress captures the IP address of commenters, so a quick web query or two later I knew that the person leaving the comment was based in Macedonia.  A glance at the web site for the GUI tool revealed that it is made by a company based in (you guessed it) Macedonia.  The comment was spam.  Polite spam.  But still.  Darn.

I considered dropping the comment, but then had a second thought.  Perhaps the reason that Log Parser never gained more attention is that (sadly) to many Windows-based developers, the command line interface is a form of Kryptonite.  Maybe a GUI is really what Log Parser needed, and maybe the commenter’s tool was worth investigating.  So I decided to give it a test drive and report my impressions here.

Log Parser Lizard: Installation and Overview

The name of the tool is Log Parser Lizard from Lizard Labs.  There is both a free version and a paid version (including a few extra features) of the application.  The listed prerequisites are Log Parser itself (obviously) and version 2.0 of the .NET Framework.  The free version of Lizard Labs Log Parser Lizard can be downloaded from http://www.lizard-labs.net/log_parser_lizard.aspx.  Tutorials and screenshots are also provided there (you might want to refer to those while reading this post).

After downloading the MSI, I started the installation process.  There were no surprises.  It was a standard Windows installation experience.  Specify a few options (where to install, etc), hit go, and it’s done.  A Log Parser Lizard group was added to the start menu; but no shortcuts were added to the desktop or elsewhere.

Upon running Log Parser Lizard the first time I was presented with an About dialog that explained the tool and asked for support in the form of blog posts, feedback, donations, or a purchased license.  Disappointing was the presence of a few grammatical and spelling errors (please register this “peace” of software?  “visit lziard-labs.net”?).  I hate to be the grammar police, but you only get one chance to make a first impression, right?  It’s worth noting that this dialog was the only place in the application where I noticed such errors.

The overall appearance of the tool is nice and clean.  The main UI has a set of panels on the left-hand side of the application labeled as follows: IIS Logs, Event Logs, Active Directory, Log4Net, File System, and T-SQL.  Selecting each of these panels reveals sets of example queries.  There are options along the top of the UI that allow users to manage the queries that appear in these panels. 

The query management option provides a way to collect and manage log parser queries, which is useful.  In my experience using Log Parser I’ve ended up with folders full of saved queries, so it would be nice to have a better way to organize them.  A downside I can see is that queries are stored within the application, and I did not find a way to export in the queries in bulk.  I imagine this would complicate moving saved queries to a second computer.

When a new query is created, or a existing query is opened (either from a query managed within the application or from an external file), additional options appear for specifying the format of the input file (examples are “IIS W3C Log”, “HTTP Error Log”, and ‘”Comma Separated Values”), setting properties of the query, and executing the query.  Query results can be viewed as a grid or a graph, and each of these output formats has multiple options.

There is one more unique feature worth mentioning.  In addition to the query management feature, there is an option to manage “Constants” which can be referenced in queries.  For example, assume that all of my IIS log files reside in a folder named \\SERVER\Share\.  I could set up a constant named “FILEPATH” and assign it a vlaue of \\SERVER\Share\.  Then, the constant could be referenced in queries like this:  “SELECT * FROM #FILEPATH#ex000000.log”.

Building A Query

When I use Log Parser, my most common use case is to query one or more IIS W3C log files.  Typically these files reside on a remote network share.  The biggest challenge I face in using Log Parser is remembering/typing the path to the log files, and remembering/typing the names of the fields to be queried.  It is easy for typos to occur, and it’s difficult to work with long queries on the command line.

So, does Log Parser Lizard help solve my biggest Log Parser challenges?  Not really.  I had expected that the GUI would allow me to browse to and select the files which I wanted to query.  I also had hoped that the tool would present me with a list of the queryable columns in the selected files.  For known file types (like the IIS W3C log format), I expected this would be possible.  Instead, as far as I could see, Log Parser queries have to be constructed largely as they would be at the command line. 

Composing queries in the tool’s UI is more convenient than composing on the command line.  However, there is no way (that I could find) to browse/select files, nor is drag-and-drop of log files possible.  In addition, the column names in the data sources still need to be typed; the tool offers no assistance.  The ability to set up constants to hold file paths and column names is useful, but not exactly the type of help I was hoping to find.

(Note: I did find an “Edit Mode” option in the UI which opens up a panel that appears to provide file browse/selection functionality.  Unfortunately, the “Add” option revealed in the panel was not active.  I could have been doing something wrong, but my assumption is that this is a feature only available to registered users of the tool.)

Executing a Query

To test the tool, I executed a simple query over a week’s worth of IIS logs for a web site on which I am lead developer.  I wanted to see a list of all of the hits on the site’s search page in that week.  Running Log Parser natively produced a 6MB CSV file containing about 11,000 rows.  The query completed in a matter of seconds.  Using Log Parser Lizard, the query took more than 10 minutes to complete.  During much of that time the CPU utilization was at 100% and most of the memory on the machine was being consumed.  Admittedly, the test was run on an underpowered (1GB RAM) virtual machine running Windows XP, but that is a dramatic difference between the command line and the GUI.

More targeted queries that return much smaller result sets seem to perform much better.

Working With Query Results

Once a query is constructed and executed, Log Parser Lizard does a nice job of presenting the results in a format that allows for quick and useful analysis.  Results can be grouped, filtered, and sorted.  Columns can be hidden and/or shown as needed.  Response time is fast, even though the initial querying of the data was quite slow. 

There are several options for outputting query results (such as Excel and PDF), but those are only available to registered users of the tool.

Conclusion

In conclusion, the free version of Log Parser Lizard does a couple things well, and a couple things not so well.  The query management feature provides a nice way to organize Log Parser queries.  The tools for analyzing query results are also solid.  On the other hand, the tool doesn’t do much to simplify construction of Log Parser queries.  And, performance of the tool for queries that return large data sets is poor.

Log Parser Lizard is a solid tool which could use some work in a few areas.  I’m not sure it will make it into my toolbox, but others might find it useful.

Log Parser Rocks! More than 50 Examples!

Log Parser is a tool that has been around for quite some time (almost six years, in fact).  I can’t really do any better than the description on the official download page, so here it is: “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”.  

Log Parser is a command line (yes, command line!) tool that uses a SQL dialect to extract information from data sources.  In particular, I have found it to be invaluable for extracting information from the web server logs of the sites that I manage and develop.

First, about that SQL syntax Log Parser uses to query the data sources… many developers seem to have a natural aversion to SQL.  In addition, many new data access frameworks attempt to abstract SQL away from the developer.  However, I have always found SQL easy to work with and believe it to be an essential tool that every developer should at least have a working knowledge of.   For Log Parser, all that is necessary is a basic understanding of the core SQL SELECT statement, as implemented within Microsoft’s SQL Server (that is, T-SQL).  That means you should be familiar with the following elements of a SELECT statement: TOP, FROM, INTO, WHERE, ORDER BY, GROUP BY.  That’s all you need to perform most Log Parser operations.

Curiously, Log Parser has never received the amount of attention that I think it deserves.  Beyond a flurry of attention when it was first released, it seems to be mentioned rarely in official Microsoft communications or blogs.  Despite that, it remains a viable and valuable tool for parsing not just web server log files, but all types of structured text-based data.

In this post, rather than explaining how to use Log Parser. I’ll give a number of examples of its use.  In addition, I’ll document some useful locations where Log Parser information can be found on the web.

Examples

Keep in mind that most of the examples that I give here are all-in-one command line queries (even though many wrap to multiple lines when displayed here).  However, queries can also be run as

logparser file:XXXXX.sql

where XXXXX is the name of a file containing a logparser-friendly sql query.  There are a couple examples of this in the following list.

The examples given here have been obtained from a variety of sources, including the documentation that ships with the tool, blogs and online documentation, and my own experience.  Unfortunately, I don’t have a record of the origin of each individual example, as I’ve compiled these piecemeal over the last two or three years.

I hope you’ll find something useful here and gain an appreciation for just how robust this tool is.

1)  All pages hits by a given IP address

logparser "select cs-uri-stem, count(cs-uri-stem) as requestcount from [LogFileName] where c-ip = ‘000.00.00.000’ group by cs-uri-stem order by count(cs-uri-stem) desc"

2) Hits on a particular page by IP address

logparser "select c-ip, count(c-ip) as requestcount from [LogFileName] where cs-uri-stem like ‘/search.aspx%’ group by c-ip order by count(c-ip) desc"

3)  ReverseDNS example.  This attempts to find the domain associated with a given IP address.

logparser "select c-ip, REVERSEDNS(c-ip) from [LogFileName] where c-ip = ‘000.00.00.000’ group by c-ip"

4)  CSV example. All hits on a page, written to a CVS file.

logparser "select * into OUTPUT.CSV from [LogFileName] where cs-uri-stem like ‘/pagename.aspx’"

5)  Chart example.  All hits on a page by an IP address, displayed on a chart.

logparser "select c-ip, count(c-ip) as requestcount into logparserchart.gif from [LogFileName] where cs-uri-stem like ‘/pagename.aspx’ group by c-ip order by count(c-ip) desc" -o:chart

6)  Hits per hour from a particular IP address

logparser "select TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time), 3600)), count(*) as numberrequests from [LogFileName] where c-ip=’000.000.00.000′ group by TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date,time), 3600))"

7)  Basic list of IP addresses generating traffic

logparser "select c-ip, count(c-ip) as requestcount from [LogFileName] group by c-ip order by count(c-ip) desc"

8)  Basic list of pages being hit

logparser "select cs-uri-stem, count(cs-uri-stem) from [LogFileName] where cs-uri-stem like ‘%aspx%’ or cs-uri-stem like ‘%ashx%’ group by cs-uri-stem order by count(cs-uri-stem) desc"

9)  Basic list of pages being hit, including which IPs are doing the hitting

logparser "select cs-uri-stem, c-ip, count(cs-uri-stem) from [LogFileName] where cs-uri-stem like ‘%aspx%’ or cs-uri-stem like ‘%ashx%’ group by cs-uri-stem, c-ip order by count(cs-uri-stem) desc"

10)  Pages being hit after a specific date and time

logparser "select cs-uri-stem, c-ip, count(cs-uri-stem) from [LogFileName] where cs-uri-stem like ‘%aspx%’ or cs-uri-stem like ‘%ashx%’ and date=’2009-06-04′ and time > ’15:00:00′ group by cs-uri-stem, c-ip order by count(cs-uri-stem) desc"

11)  Counts of hits of ASPX/ASHX pages by hour from a particular IP address

logparser "select TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time), 3600)), count(*) as numberrequests from [LogFileName] where c-ip=’000.000.00.00′ and (cs-uri-stem like ‘%aspx%’ or cs-uri-stem like ‘%ashx%’) group by TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date,time), 3600))"

12)  Counts of hits against specific pages by hour from a particular IP address

logparser "select TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time), 3600)), cs-uri-stem, count(*) as numberrequests from [LogFileName] where c-ip=’000.000.00.00′ and (cs-uri-stem like ‘%aspx%’ or cs-uri-stem like ‘%ashx%’) group by TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date,time), 3600)), cs-uri-stem order by numberrequests desc"

13)  Top browsers

logparser "Select top 50 to_int(mul(100.0,PropCount(*))) as Percent, count(*) as TotalHits, cs(User-Agent) as Browser from [LogFileName] group by Browser order by Totalhits desc"

14)  Hourly Bandwidth (chart)

logparser "Select TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time), 3600)) As Hour, Div(Sum(cs-bytes),1024) As Incoming(K), Div(Sum(sc-bytes),1024) As Outgoing(K) Into BandwidthByHour.gif From [LogFileName] Group By Hour"

15)  Requests by URI

logparser "SELECT top 80 QUANTIZE(TO_TIMESTAMP(date, time), 3600) as Hour, TO_LOWERCASE(STRCAT(‘/’,EXTRACT_TOKEN(cs-uri-stem,1,’/’))) as URI, COUNT(*) AS RequestsPerHour, SUM(sc-bytes) AS TotBytesSent, AVG(sc-bytes) AS AvgBytesSent, Max(sc-bytes) AS MaxBytesSent, ADD(1,DIV(Avg(time-taken),1000)) AS AvgTime, ADD(1,DIV(MAX(time-taken),1000)) AS MaxTime FROM [LogFileName] GROUP BY Hour, URI Having RequestsPerHour > 10 ORDER BY RequestsPerHour ASC"

16)  Top 10 Images by size

logparser "Select Top 10 StrCat(Extract_Path(TO_Lowercase(cs-uri-stem)),’/’) AS RequestedPath, Extract_filename(To_Lowercase(cs-uri-stem)) As RequestedFile, Count(*) AS Hits, Max(time-taken) As MaxTime, Avg(time-taken) As AvgTime, Max(sc-bytes) As BytesSent From [LogFileName] Where (Extract_Extension(To_Lowercase(cs-uri-stem)) IN (‘gif’;’jpg’;’png’)) AND (sc-status = 200) Group By To_Lowercase(cs-uri-stem) Order By BytesSent, Hits, MaxTime DESC"

17)  Top 10 URLs for a website, with total hits, max time to serve, and average time to serve

logparser "Select TOP 10 STRCAT(EXTRACT_PATH(cs-uri-stem),’/’) AS RequestPath, EXTRACT_FILENAME(cs-uri-stem) AS RequestedFile, COUNT(*) AS TotalHits, Max(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime, AVG(sc-bytes) AS AvgBytesSent FROM [LogFileName] GROUP BY cs-uri-stem ORDER BY TotalHits DESC"

18)  Top 20 clients

logparser "Select Top 20 c-ip AS Client, Count(*) AS Hits INTO Chart.gif FROM [LogFileName] GROUP BY c-ip ORDER BY Hits Desc"

19)  Referrer Broken Links (i.e. external references to broken links on your site)

logparser "SELECT DISTINCT cs(Referer) as Referer, cs-uri-stem as Url INTO ReferBrokenLinks.html FROM [LogFileName] WHERE cs(Referer) IS NOT NULL AND sc-status = 404 AND (sc-substatus IS NULL OR sc-substatus=0)" -tpl:ReferBrokenLinks.tpl

20)  Status codes

logparser "SELECT sc-status As Status, COUNT(*) As Number INTO StatusCodes.gif FROM <2> GROUP BY Status ORDER BY Status"

21)  Search the Event Log for W3SVC (IIS) log entries and color-coordinate as to Error, Warning, Information.  This example writes the output of the query to an HTML file that  is generated using a template file.

logparser "SELECT TimeGenerated,EventTypeName,Strings,Message,CASE EventTypeName WHEN ‘Error event’ THEN ‘RED’ WHEN ‘Warning event’ THEN ‘YELLOW’ WHEN ‘Information event’ THEN ‘WHITE’ ELSE ‘BLUE’ END As Color INTO file.html FROM System WHERE SourceName = ‘W3SVC’"  -tpl:IISEventLogEntries.tpl

Where IISEventLogEntries.tpl is a file that contains the following:

<LPHEADER>
<HTML>
<HEAD>
  <STYLE>
    TD { font-family: Arial };
    TH { font-family: Arial };
  </STYLE>
</HEAD>
<BODY>
<TABLE BORDERCOLOR="BLACK" BORDER="1" CELLPADDING="2" CELLSPACING="2">
<TR>
  <TH COLSPAN=4 BGCOLOR="BLACK"><FONT COLOR=WHITE>New W3SVC Messages in System Event Log</FONT></TH>
</TR>
<TR>
  <TH ALIGN=LEFT BGCOLOR="#C0C0C0">Time Generated</TH>
  <TH ALIGN=LEFT BGCOLOR="#C0C0C0">Event Type</TH>
  <TH ALIGN=LEFT BGCOLOR="#C0C0C0">Strings</TH>
  <TH ALIGN=LEFT BGCOLOR="#C0C0C0">Message</TH>
</TR>
</LPHEADER>
<LPBODY>
<TR bgCOLOR="%Color%">
  <TD>%TimeGenerated%</TD>
  <TD>%EventTypeName%</TD>
  <TD>%Strings%</TD>
  <TD>%Message%</TD>
</TR>
</LPBODY>
</TABLE>
</BODY>
</HTML>

22)  Upload Log Parser query results directly to a table in SQL Server

logparser "select * into LogTable from [LogFileName] where cs-uri-stem like ‘/folder/filename%’" -o:SQL -createTable:ON -server:[DatabaseServer] -database:[Database] -username:[SqlUser] -password:[SqlPassword]

23)  Top 10 images by size sent.  Note that this example also shows how to query multiple log files at once.

logparser "Select Top 10 StrCat(Extract_Path(TO_Lowercase(cs-uri-stem)),’/’) AS RequestedPath, Extract_filename(To_Lowercase(cs-uri-stem)) As RequestedFile, Count(*) AS Hits, Max(time-taken) As MaxTime, Avg(time-taken) As AvgTime, Max(sc-bytes) As BytesSent INTO TOP10ImagesBySize.txt FROM logs\iis\ex*.log WHERE (Extract_Extension(To_Lowercase(cs-uri-stem)) IN  (‘gif’;’jpg’;’png’)) AND (sc-status = 200) GROUP BY To_Lowercase(cs-uri-stem) ORDER BY BytesSent, Hits, MaxTime DESC"

24)  Browser types (two different approaches)

logparser "SELECT distinct cs(User-Agent), count(*) as hits INTO useragentsalltypes.txt FROM logs\iis\ex*.log GROUP BY cs(user-agent) ORDER BY hits DESC"

logparser "SELECT TO_INT(MUL(100.0,PROPCOUNT(*))) AS Percent,  COUNT(*) AS Hits, cs(User-Agent) as Browser INTO  UseragentsHits.txt FROM  logs\iis\ex*.log  GROUP BY Browser ORDER BY HITS DESC"

25)  Unique visitors per day.  This requires two queries.  The first query selects from the IIS logs into a CSV file, and the second selects from that CSV file.

logparser "SELECT DISTINCT cs-username, date INTO tempUniqueVisitorsPerDay.csv FROM logs\iis\ex*.log WHERE cs-username <> NULL Group By Date, cs-username"

logparser "SELECT date, count(cs-username) as UniqueVisitors into test.txt FROM tempUniqueVisitorsPerDay.csv GROUP BY date"

26)  Top 10 largest ASPX pages.

logparser "Select Top 10 StrCat(Extract_Path(TO_Lowercase(cs-uri-stem)),’/’) AS  RequestedPath, Extract_filename(To_Lowercase(cs-uri-stem)) As RequestedFile,  Count(*) AS Hits, Max(time-taken) As MaxTime, Avg(time-taken) As AvgTime, Max(sc-bytes) As BytesSent INTO top10pagesbysize.txt FROM logs\iis\ex*.log WHERE (Extract_Extension(To_Lowercase(cs-uri-stem)) IN (‘aspx’)) AND  (sc-status = 200) GROUP BY To_Lowercase(cs-uri-stem) ORDER BY BytesSent, Hits, MaxTime DESC"

27)  Top 10 slowest ASPX pages

logparser "SELECT TOP 10 cs-uri-stem, max(time-taken) as MaxTime, avg(time-taken) as AvgTime INTO toptimetaken.txt FROM logs\iis\ex*.log WHERE extract_extension(to_lowercase(cs-uri-stem)) = ‘aspx’ GROUP BY cs-uri-stem ORDER BY MaxTime DESC"

28)  Top 10 slowest ASPX pages on a specific day

logparser "SELECT TOP 10 cs-uri-stem, max(time-taken) as MaxTime, avg(time-taken) as AvgTime INTO toptimetaken.txt FROM logs\iis\ex*.log WHERE extract_extension(to_lowercase(cs-uri-stem)) = ‘aspx’ AND TO_STRING(To_timestamp(date, time), ‘MMdd’)=’1003’  GROUP BY cs-uri-stem ORDER BY MaxTime DESC"

29)  Daily bandwidth

logparser "Select To_String(To_timestamp(date, time), ‘MM-dd’) As Day, Div(Sum(cs-bytes),1024) As Incoming(K), Div(Sum(sc-bytes),1024) As Outgoing(K) Into BandwidthByDay.gif From logs\iis\ex*.log Group By Day"

30)  Bandwidth by hour

logparser "SELECT QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS Hour, SUM(sc-bytes) AS TotalBytesSent INTO BytesSentPerHour.gif FROM logs\iis\ex*.log GROUP BY Hour ORDER BY Hour"

31)  Average page load time per user

logparser "Select Top 20 cs-username AS UserName, AVG(time-taken) AS AvgTime,  Count(*) AS Hits INTO AvgTimePerUser.txt FROM logs\iis\ex*.log WHERE cs-username IS NOT NULL GROUP BY cs-username ORDER BY AvgTime DESC"

32)  Ave page load time for a specific user

logparser "Select cs-username AS UserName, AVG(time-taken) AS AvgTime,  Count(*) AS Hits INTO AvgTimeOnSpecificUser.txt FROM logs\iis\ex*.log WHERE cs-username = ‘CONTOSO\User1234’ GROUP BY cs-username"

33)  Error trends.  This query is quite long, and is easier expressed in a text file than on the command line.  So, Log Parser reads and executes the query contained in the specified text file.

logparser file:errortrend.sql

Where errortrend.sql contains the following:

SELECT
  TO_STRING(To_timestamp(date, time), ‘MMdd’) AS Day,
  SUM(c200) AS 200s,
  SUM(c206) AS 206s,
  SUM(c301) AS 301s,
  SUM(c302) AS 302s,
  SUM(c304) AS 304s,
  SUM(c400) AS 400s,
  SUM(c401) AS 401s,
  SUM(c403) AS 403s,
  SUM(c404) AS 404s,
  SUM(c500) AS 500s,
  SUM(c501) AS 501s,
  SUM(c502) AS 502s,
  SUM(c503) AS 503s,
  SUM(c504) AS 504s,
  SUM(c505) AS 505s
USING
  CASE sc-status WHEN 200 THEN 1 ELSE 0 END AS c200,
  CASE sc-status WHEN 206 THEN 1 ELSE 0 END AS c206,
  CASE sc-status WHEN 301 THEN 1 ELSE 0 END AS c301,
  CASE sc-status WHEN 302 THEN 1 ELSE 0 END AS c302,
  CASE sc-status WHEN 304 THEN 1 ELSE 0 END AS c304,
  CASE sc-status WHEN 400 THEN 1 ELSE 0 END AS c400,
  CASE sc-status WHEN 401 THEN 1 ELSE 0 END AS c401,
  CASE sc-status WHEN 403 THEN 1 ELSE 0 END AS c403,
  CASE sc-status WHEN 404 THEN 1 ELSE 0 END AS c404,
  CASE sc-status WHEN 500 THEN 1 ELSE 0 END AS c500,
  CASE sc-status WHEN 501 THEN 1 ELSE 0 END AS c501,
  CASE sc-status WHEN 502 THEN 1 ELSE 0 END AS c502,
  CASE sc-status WHEN 503 THEN 1 ELSE 0 END AS c503,
  CASE sc-status WHEN 504 THEN 1 ELSE 0 END AS c504,
  CASE sc-status WHEN 505 THEN 1 ELSE 0 END AS c505
INTO ErrorChart.gif
FROM
    logs\iis\ex*.log
GROUP BY
  Day
ORDER BY
  Day

34)  Win32 errors

logparser "SELECT sc-win32-status as ErrorNumber, WIN32_ERROR_DESCRIPTION(sc-win32-status) as ErrorDesc, Count(*) AS Total INTO Win32ErrorNumbers.txt FROM logs\iis\ex*.log WHERE sc-win32-status>0 GROUP BY ErrorNumber ORDER BY Total DESC"

35)  Substatus codes

logparser "SELECT sc-status, sc-substatus, Count(*) AS Total INTO 401subcodes.txt FROM logs\iis\ex*.log WHERE sc-status=401 GROUP BY sc-status, sc-substatus ORDER BY sc-status, sc-substatus DESC"

36)  Substatus codes per day.  This is another example of executing a query contained in a text file.

logparser file:substatusperday.sql

Where substatusperday.sql contains the following:

SELECT
  TO_STRING(To_timestamp(date, time), ‘MMdd’) AS Day,
  SUM(c1) AS 4011,
  SUM(c2) AS 4012,
  SUM(c3) AS 4013,
  SUM(c4) AS 4014,
  SUM(c5) AS 4015,
  SUM(c7) AS 4017
USING
  CASE sc-substatus WHEN 1 THEN 1 ELSE 0 END AS c1,
  CASE sc-substatus WHEN 2 THEN 1 ELSE 0 END AS c2,
  CASE sc-substatus WHEN 3 THEN 1 ELSE 0 END AS c3,
  CASE sc-substatus WHEN 4 THEN 1 ELSE 0 END AS c4,
  CASE sc-substatus WHEN 5 THEN 1 ELSE 0 END AS c5,
  CASE sc-substatus WHEN 7 THEN 1 ELSE 0 END AS c7
INTO
  401subcodesperday.txt
FROM
  logs\iis\ex*.log
WHERE
  sc-status=401
GROUP BY
  Day
ORDER BY
  Day

37)  Substatus codes per page

logparser "SELECT TOP 20 cs-uri-stem, sc-status, sc-substatus, Count(*) AS Total INTO 401Pagedetails.txt FROM logs\iis\ex*.log WHERE sc-status=401 GROUP BY cs-uri-stem, sc-status, sc-substatus ORDER BY Total"

38)  MB sent per HTTP status code

logparser "SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, SUM(sc-bytes) as TotalBytesSent, TO_INT(MUL(PROPSUM(sc-bytes), 100.0)) AS PercentBytes INTO PagesWithLargestBytesSent.htm FROM logs\iis\ex*.log GROUP BY Pagetype ORDER BY PercentBytes DESC"

39) 500 errors per ASPX and Domain User

logparser "SELECT cs-username, cs-uri-stem, count(*) as Times INTO 500PagesByUserAndPage.txt FROM logs\iis\ex*.log WHERE sc-status=500 GROUP BY  cs-username, cs-uri-stem ORDER BY Times DESC"

40)  Percent of 500 errors caused by each user

logparser "SELECT cs-username, count(*) as Times, propcount(*) as Percent INTO 500ErrorsByUser.csv FROM  logs\iis\ex*.log WHERE sc-status=500 GROUP BY cs-username ORDER BY Times DESC"

41)  Determine what percentage of the total bytes sent are being caused by each page type

logparser "SELECT EXTRACT_EXTENSION(cs-uri-stem) AS PageType, SUM(sc-bytes) as TotalBytesSent, TO_INT(MUL(PROPSUM(sc-bytes), 100.0)) AS PercentBytes INTO PagesWithLargestBytesSent.txt FROM logs\iis\ex*.log GROUP BY Pagetype ORDER BY PercentBytes DESC"

42)  Top 20 pages with a specific HTTP return code

logparser "SELECT TOP 20 cs-uri-stem, sc-status, Count(*) AS Total INTO TOP20PagesWith401.txt FROM logs\iis\ex*.log WHERE TO_LOWERCASE(cs-uri-stem) LIKE ‘%.aspx’ and sc-status=401 GROUP BY cs-uri-stem, sc-status ORDER BY Total, cs-uri-stem, sc-status DESC"

43)  Check traffic from IP addresses

logparser "Select c-ip AS Client, Div(Sum(cs-bytes),1024) As IncomingBytes(K), Div(Sum(sc-bytes),1024) As OutgoingBytes(K), MAX(time-taken) as MaxTime, AVG(time-taken) as AvgTime, count(*) as hits INTO errorsperip.txt FROM logs\iis\ex*.log GROUP BY client ORDER BY Hits DESC"

44)  Check errors by IP address

logparser file:errorbyip.sql

Where errorbyip.sql contains the following:

Select
  c-ip AS Client,
  SUM(c400) AS 400s,
  sum(c401) AS 401s,
  SUM(c403) AS 403s,
  SUM(c404) AS 404s,
  SUM(c500) AS 500s,
  SUM(c501) AS 501s,
  SUM(c502) AS 502s,
  SUM(c503) AS 503s,
  SUM(c504) AS 504s,
  SUM(c505) AS 505s
USING
  CASE sc-status WHEN 400 THEN 1 ELSE 0 END AS c400,
  CASE sc-status WHEN 401 THEN 1 ELSE 0 END AS c401,
  CASE sc-status WHEN 403 THEN 1 ELSE 0 END AS c403,
  CASE sc-status WHEN 404 THEN 1 ELSE 0 END AS c404,
  CASE sc-status WHEN 500 THEN 1 ELSE 0 END AS c500,
  CASE sc-status WHEN 501 THEN 1 ELSE 0 END AS c501,
  CASE sc-status WHEN 502 THEN 1 ELSE 0 END AS c502,
  CASE sc-status WHEN 503 THEN 1 ELSE 0 END AS c503,
  CASE sc-status WHEN 504 THEN 1 ELSE 0 END AS c504,
  CASE sc-status WHEN 505 THEN 1 ELSE 0 END AS c505
INTO
  IPNumberFileName.txt
FROM
    logs\iis\ex*.log
WHERE
    c-ip='<IP address goes here>’
GROUP BY
    client

45)  Find broken links

logparser "SELECT DISTINCT cs(Referer) as Referer, cs-uri-stem as Url INTO ReferBrokenLinks.txt FROM logs\iis\ex*.log WHERE cs(Referer) IS NOT NULL AND sc-status=404 AND (sc-substatus IS NULL OR sc-substatus=0)"

46)  Top 10 pages with most hits

logparser "Select TOP 10 STRCAT(EXTRACT_PATH(cs-uri-stem),’/’) AS RequestPath, EXTRACT_FILENAME(cs-uri-stem) AS RequestedFile, COUNT(*) AS TotalHits, Max(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime, AVG(sc-bytes) AS AvgBytesSent INTO Top10Urls.txt FROM logs\iis\ex*.log GROUP BY cs-uri-stem ORDER BY TotalHits DESC"

47)  Unique users per browser type (requires two queries)

logparser "SELECT DISTINCT cs-username, cs(user-agent) INTO UserAgentsUniqueUsers1.csv FROM logs\iis\ex*.log WHERE cs-username <> NULL GROUP BY cs-username, cs(user-agent)"

logparser "SELECT cs(user-agent), count(cs-username) as UniqueUsersPerAgent, TO_INT(MUL(PROPCOUNT(*), 100)) AS Percentage INTO UniqueUsersPerAgent.txt FROM UserAgentsUniqueUsers1.csv GROUP BY  cs(user-agent) ORDER BY UniqueUsersPerAgent DESC"

48)  Bytes sent per file extension

logparser "SELECT EXTRACT_EXTENSION( cs-uri-stem ) AS Extension, MUL(PROPSUM(sc-bytes),100.0) AS PercentageOfBytes, Div(Sum(sc-bytes),1024) as AmountOfMbBytes INTO BytesPerExtension.txt FROM logs\iis\ex*.log GROUP BY Extension ORDER BY PercentageOfBytes DESC"

49)  Domains referring traffic to your site

logparser "SELECT EXTRACT_TOKEN(cs(Referer), 2, ‘/’) AS Domain, COUNT(*) AS [Requests] INTO ReferringDomains.txt FROM  logs\iis\ex*.log GROUP BY Domain ORDER BY Requests DESC"

50)  OS types (requires two queries)

logparser "SELECT DISTINCT c-ip, cs(user-agent) INTO UserAgentsUniqueUsers.csv FROM logs\iis\ex*.log WHERE c-ip <> NULL GROUP BY c-ip, cs(user-agent)"

logparser file:getos.sql

Where getos.sql contains the following:

SELECT
  SUM (c70) AS Win7,
  SUM (c60) AS Vista,
  SUM (c52) AS Win2003,
  SUM (c51) AS WinXP,
  SUM (C50) AS Win2000,
  SUM (W98) AS Win98,
  SUM (W95) AS Win95,
  SUM (W9x) AS Win9x,
  SUM (NT4) AS WinNT4,
  SUM (OSX) AS OS-X,
  SUM (Mac) AS Mac-,
  SUM (PPC) AS Mac-PPC,
  SUM (Lnx) AS Linux
USING
  CASE strcnt(cs(User-Agent),’Windows+NT+6.1′) WHEN 1 THEN 1 ELSE 0 END AS C70,
  CASE strcnt(cs(User-Agent),’Windows+NT+6.0′) WHEN 1 THEN 1 ELSE 0 END AS C60,
  CASE strcnt(cs(User-Agent),’Windows+NT+5.2′) WHEN 1 THEN 1 ELSE 0 END AS C52,
  CASE strcnt(cs(User-Agent),’Windows+NT+5.1′) WHEN 1 THEN 1 ELSE 0 END AS C51,
  CASE strcnt(cs(User-Agent),’Windows+NT+5.0′) WHEN 1 THEN 1 ELSE 0 END AS C50,
  CASE strcnt(cs(User-Agent),’Win98′) WHEN 1 THEN 1 ELSE 0 END AS W98,
  CASE strcnt(cs(User-Agent),’Win95′) WHEN 1 THEN 1 ELSE 0 END AS W95,
  CASE strcnt(cs(User-Agent),’Win+9x+4.90′) WHEN 1 THEN 1 ELSE 0 END AS W9x,
  CASE strcnt(cs(User-Agent),’Winnt4.0′) WHEN 1 THEN 1 ELSE 0 END AS NT4,
  CASE strcnt(cs(User-Agent),’OS+X’) WHEN 1 THEN 1 ELSE 0 END AS OSX,
  CASE strcnt(cs(User-Agent),’Mac’) WHEN 1 THEN 1 ELSE 0 END AS Mac,
  CASE strcnt(cs(User-Agent),’PPC’) WHEN 1 THEN 1 ELSE 0 END AS PPC,
  CASE strcnt(cs(User-Agent),’Linux’) WHEN 1 THEN 1 ELSE 0 END AS Lnx
INTO
  GetOSUsed.txt
FROM
  UserAgentsUniqueUsers.csv

51)  Get timeout errors from the server Event Log.  Display results in a datagrid.

logparser "select * from \\servername\application where message like ‘%timeout expired%’" -i:EVT -o:datagrid

52)  Get exceptions from the server Event (Application) Log

logparser "select timegenerated, eventtypename, eventcategoryname, message into webserverlog.csv from \\servername\application where message like ‘%myapplication%exception%’" -i:EVT

Links

Check out the links below to find more in-depth discussion of Log Parser, as well as even more examples of its usage.