Log Parser and Reporting

Posted by Techie Cocktail | 1:18 AM | | 0 comments »

Introduction:
Logging information has become integral part of the source code implementation as it helps to identify useful information or troubleshoot an issue. Logs can contain lots of information which may be sometime tedious to interpret, specially, if they contain too much of data. It can sometime become difficult or time-consuming to understand the large number of records in many log files and sometime the data isn't in human-readable format.

Solution:
Log Parser is one of the available free tools provided by Microsoft that can be used to parse the log data efficiently and represent it in one of its available output formats. It uses a SQL-like engine core to process data from logs and generates custom results. It means that it uses SQL language with built-in log parser functions to query the logs and get the desired results.

Some of the log formats it supports are - XML, CSV, Windows Event Logs, IIS Logs, Windows Registry, Active Directory etc. The output can be presented as a chart, datagrid, HTML, XML etc.

The Log Parser tool is available as:
a. Command-line tool:
http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

b. Easy-to-use Visual Log Parser:
http://www.serialcoder.net/deploy/visual-logparser/publish.htm

Sample scripts:

a. Top 20 IP Address & their count from where your site was looked upon - output results to datagrid.

Logparser –i:IISW3C "select Top 20 Date, c-ip as IPAddress, count(*) as [Count]
from <iislogfile>
group by Count, Date, IPAddressorder by [Count] desc" -o:datagrid

b. Browser Breakup – output results to html using a template:

The output can be presented in the html format by attaching a template fileTemplate:
<LPHEADER>
<HTML>
<HEAD><TITLE>Browser Breakup Report</TITLE></HEAD>
<BODY>
<H1>Browser Breakup Report</H1>
<TABLE BORDER="1">
<TR BGCOLOR="GRAY">
<TH>Browser</TH>
<TH>Hits</TH>
</TR>
</LPHEADER>

<LPBODY>
<TR>
<TD><TT>%Browser%</TT></TD>
<TD><TT>%Hits%</TT></TD>
</TR>
</LPBODY>
</TABLE>
<LPFOOTER>
</BODY>
</HTML>
</LPFOOTER>

Query:

Logparser –i:IISW3C "Select top 50 cs(User-Agent) as Browser, count(*) as Hits
into <report.html>
from <iislogfile>
group by Browser
order by Hits desc"
-stats:OFF -o:TPL -tpl:template.txt

c. Event Viewer Logs – output results to an xml file:

Logparser –i:EVT “SELECT TimeGenerated AS Date, EventTypeName as [Event Type],
EventID, SourceName AS Source,
EventCategoryName AS Category, ComputerName AS Computer, Message
Into <iislogfile>FROM system, Application
where EventType IN (1;2) ORDER BY TimeGenerated DESC”

d. Dumping data to SQL Server directly – output result to SQL Table:

Logparser –i:IISW3C "SELECT sc-status as Status, sc-substatus as Sub-Status, COUNT(*) as [Count]
into SQLTABLE from <iislogfile> GROUP BY sc-status, sc-substatus ORDER BY [Count] DESC”-o:SQL –database:SQLDatabase

e. Status & Sub-Status Code Distribution – output results to Excel:

Logparser –i:IISW3C "SELECT sc-status as Status, sc-substatus as Sub-Status, COUNT(*) as [Count]
into <excel.csv> from <iislogfile> GROUP BY sc-status, sc-substatus ORDER BY [Count] DESC”

Reporting options:
a. Output in the form of html by using a base template.txt attached to it. A chart can also be embedded into the html document by adding the <img> tag within the template.

b. Output data to the SQL Table directly and use SSRS and generate simple, matrix reports and using in-built or Dundas charts.

c. Output the result in the form of XML data. Attach an XSLT file to the xml and transform the output in the form of example.

These are just a few pointers to endless ways to parse and implement reporting. Also, there is a very good help provided in the Help Menu of the visual log parser (VLP). The VLP help shows the complete solid list of functions that can help you to retrieve any possible data you want to. If someone likes the command-line, go for "Logparser –h /?" for help.

0 comments