Logparser and Charts

I'll start off with this subject with some of the basics, then I'll try and get a bit more complex and perhaps even write up a helper class.

Logparser uses OWC (only 2003 and earlier...)which is a blessing and a curse. It is a curse since not much is out there on OWC but a blessing in that OWC has a lot of charts! Having said this, logparser has OWC built in, but that should not stop any developer from sending output to a jquery or flash charting application and charting it that way. It is just easier with OWC. After you install the OWC on your server, here is a quick script to see if it works:

COMTSVInputContextClassClass inputFormat = new COMTSVInputContextClassClass();
chartOuputFormat = new COMChartOutputContextClassClass();
chartOuputFormat.chartType = "ColumnStacked";

string query = "select * from abc.log"
LogQueryClass logQuery = new LogQueryClassClass();

logQuery.ExecuteBatch(query, inputFormat, chartOuputFormat);

The above code is hacked up and I did not really test it, but should do the trick. I'll start posting more detailed helper classes this week.

Charting with Logparser and C#

I have been asked to show some examples on how to use logparser with charts and specifically from c#. I will try and post those up this weekend.

LogParser Examples in c#

Seems like many people are downloading my examples in c# for logparser. I will work on some more examples and post them up for you guys.

Logparser and Team Foundation Server

Gluegood has posted a really cool tool on this site. In short this tool allows for:

• Querying and notification of recent work items
• Querying and notification of the most recent check-ins
• Querying and notification of check-outs which were over a time threshold
• Querying and notification of shelvesets (parked code) which expired a certain amount of time
• Querying and notification across linked work items

Some of this is available right in Team Server, but some of it does not really exist easily. This tool created will allow for all the above and will get you to learn some cool new features of Team Server.

LogParser TSV and C#

Someone asked me how to get TSV format using C#. Here is an example:

COMTSVInputContextClassClass input = LogParserOptions.CreateTSVInputFormat();

input.iCheckpoint = "Test.checkpoint";
input.nSkipLines = 5;
input.headerRow = false;
input.iSeparator = " ";
input.nSep = 3;
input.dtLines = 0;
input.codepage = -1;

return RunScript(scriptName, input);

//You will have to create a List of columns List or some option like this...this is what my RunScript looks like
public MyRecords RunScript(string queryName, object inputFormat)
{
 return RunScript(queryName, null, inputFormat);
}

HTTP Status Codes Class

One of my apps required to graph and chart IIS logs. I of course was using logparser to do this and everything seemed to be working out. The request changed on me slightly so that I can define what the status code is as opposed to just saying the number of 404's or 200's. Here is an easy Dictionary class that you can use:

public class HttpStatusCodes
{
    static Dictionary _map = ConstructStatusCodesMap();

    public static string GetDescription(int statusCode)
    {
        string description = "";
        if (_map.ContainsKey(statusCode))
            description = _map[statusCode];

        return string.Format("{0} - {1}", statusCode, description);
    }

    private static Dictionary ConstructStatusCodesMap()
    {
        Dictionary map = new Dictionary();

        // http://status-code.com/
        #region Construct HTTP status codes map

        map.Add(100, "Continue");
        map.Add(101, "Switching Protocols");
        map.Add(102, "Processing");
        map.Add(200, "OK");
        map.Add(201, "Created");
        map.Add(202, "Accepted");
        map.Add(203, "Non-Authoritative Information");
        map.Add(204, "No Content");
        map.Add(205, "Reset Content");
        map.Add(206, "Partial Content");
        map.Add(207, "Multi-Status");
        map.Add(226, "IM Used");
        map.Add(300, "Multiple Choices");
        map.Add(301, "Moved Permanently");
        map.Add(302, "Found");
        map.Add(303, "See Other");
        map.Add(304, "Not Modified");
        map.Add(305, "Use Proxy");
        map.Add(306, "(Unused)");
        map.Add(307, "Temporary Redirect");
        map.Add(400, "Bad Request");
        map.Add(401, "Unauthorized");
        map.Add(402, "Payment Required");
        map.Add(403, "Forbidden");
        map.Add(404, "Not Found");
        map.Add(405, "Method Not Allowed");
        map.Add(406, "Not Acceptable");
        map.Add(407, "Proxy Authentication Required");
        map.Add(408, "Request Timeout");
        map.Add(409, "Conflict");
        map.Add(410, "Gone");
        map.Add(411, "Length Required");
        map.Add(412, "Precondition Failed");
        map.Add(413, "Request Entity Too Large");
        map.Add(414, "Request-URI Too Long");
        map.Add(415, "Unsupported Media Type");
        map.Add(416, "Requested Range Not Satisfiable");
        map.Add(417, "Expectation Failed");
        map.Add(418, "I'm a teapot");
        map.Add(422, "Unprocessable Entity");
        map.Add(423, "Locked");
        map.Add(424, "Failed Dependency");
        map.Add(425, "(Unordered Collection)");
        map.Add(426, "Upgrade Required");
        map.Add(500, "Internal Server Error");
        map.Add(501, "Not Implemented");
        map.Add(502, "Bad Gateway");
        map.Add(503, "Service Unavailable");
        map.Add(504, "Gateway Timeout");
        map.Add(505, "HTTP Version Not Supported");
        map.Add(506, "Variant Also Negotiates");
        map.Add(507, "Insufficient Storage");
        map.Add(510, "Not Extended");

        #endregion

        return map;
    }
}

Using it would be something like:

 return HttpStatusCodes.GetDescription((int)value);

Microsoft LogParser 2.2 Tutorial Update...

Someone pointed out to me that Microsoft LogParser 2.2 Tutorial VIII download link was wrong...I updated it in the post. You can find the download here. Sorry about that.

Microsoft LogParser 2.2 Tutorial X

Click here to see part IX.

I went through a ton of things regarding logparser over the last week or so. I wanted to end the tutorials with some more queries that I have used. Comment here if you have any specific things you would like to parse.

• LogParserPlus: Collection of some nice queries
• Parse FileZilla Server logs
• LogParser + Eventcomb to find malware
• LogParser to analyze Exchange

Below, I will post answers to some questions that I received via email:

How do I pass multiple params to logparer query?

--http://blogs.msdn.com/adcman/archive/2006/05/15/598149.aspx
logparser file:query.sql?a=1+b=2

How do I split date and time in a logparser query?

logparser "SELECT TO_DATE(DateTime) As Date, TO_TIME(TO_LOCALTIME(DateTime)) As Time...."

Can I query with logparser and not modify LastAccess on a file?
Yes, the default is off, but you can pass a param to your query.

-preserveLastAccTime ON|OFF (default is OFF)

Microsoft LogParser 2.2 Tutorial IX

Click here to see part VIII.

With only 1-2 more tutorials to go, I wanted to give some references to where you can find information and resources on Logparser. The first place to go for all the questions you may have is to the IIS Forums for LogParser. There are however some other resources which I am listing below:

Books:

• Microsoft LogParser Toolkit -- The ultimate bible written by Gabriele himself
• IIS 7.0 Resource Kit -- The resource kit book for IIS 7 (you can look at IIS 6 as well)
• Windows Forensics Book -- Many exist on forensics, this is a good one

Websites:

• Rahul Soni's Blog -- Has some good examples
• TechNet
• Steve Schofield's Blog
• Linux Lore -- One of the first sites I used to help me get started
• IIS7 Logparser Gadget
• GlueGood's Blog -- One of the BEST sites for full completed examples
  

User Interfaces:

• LogParser Lizard -- Probably the best one out there
  
• Bob Bacus GUI
• Visual LogParser
• Ajax Lab Command Prompt for LogParser -- ASP.NET based

I'll probably post one more tutorial with some more sample queries.

Microsoft LogParser 2.2 Tutorial VIII

Click here to see part VII.

As we come to the end of the logparser tutorials, I wanted to show a basic C# library that can be extended for your needs. This library had a lot of input from various co-workers and blogs. This version of the library is an old stripped down version since the newer version has a lot more code that is propitiatory. I am sure you can extend this and would love to see what you come up with. You can find the codebase for the sample below here.

The main idea of this is to have a simple console application where you can drop a log file and a query and parse it in C# using LogParser. This can be extended to go into an Asp.Net application or a windows GUI for a business user.

Shown below is a snippet of the very basic console.

 class Program
  {
      static void Main(string[] args)
      {
          new Program().Run();
      }

      void Run()
      {
          W3cView _view = new W3cView();
          W3cQueries _queries = new W3cQueries();

          //Each query is listed below
          _view.CountOfTotalRequests(_queries.CountTotalRequests());
          _view.Table(_queries.Top10HitsWithExtensionAspx());
          Console.ReadLine();
      }
  }

The W3C query file which has the list of the sql files that are called looks like this:

 class W3cQueries
  {
      QueriesHelper queriesHelper = new QueriesHelper();

      public int CountTotalRequests()
      {
          return queriesHelper.GetInt("CountTotalRequests.sql");
      }

      public LogRecords Top20HitsWithExtensionAspx()
      {
          return queriesHelper.GetRecords("Top10HitsWithExtensionAspx.sql");
      }     
    
      //Add more queries
  }

I would love to see any extensions that any of you come up with based off this.

LogParser Matrix Style

One last fun output:

logparser
"select * from security"
-o:neuroview

Perhaps not the best output format, but nice anyway

Ruby Wrapper for LogParser

Since we are on the topic of logparser, I happened to come across this today. The project description:

" Ruby Log Parser is a wrapper around Microsoft's Log Parser tool freely available for Windows. This wrapper enables a SQL statement to be passed to the LogParser executable and the results returned via an array or hash for further processing in Ruby. "

Very cool!

Microsoft LogParser 2.2 Tutorial VII

Click here to see part VI.

We have seen how to call logparser from both powershell and C#. Let's show how it can be called from 2 other scripting languages:

VBScript:

Set objLogParser = CreateObject("MSUtil.LogQuery")
Set objInputFormat = CreateObject("MSUtil.LogQuery.IISW3CInputFormat")
Set objOutputFormat = CreateObject("MSUtil.LogQuery.W3COutputFormat")

outputfile = "c:\temp.log"

strQuery = "select date, time, s-sitename, s-computername, s-ip, cs-method, cs-uri-stem, cs-uri-query, s-port, cs-username, c-ip, cs(User-Agent), cs(Cookie), cs(Referer), cs-host, sc-status, sc-substatus, sc-win32-status, time-taken FROM d:\logs\ex*.log"

objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat
'to iterate through a recordset modify to
'Set objRecordSet = objLogParser.Execute (strQuery, objInputFormat)

Set objShell = Wscript.CreateObject("Wscript.Shell")
objShell.Run outputfile

JScript: (documented sample in .chm)

var oLogQuery = new ActiveXObject("MSUtil.LogQuery");

// Make sure that parse error messages are collected
oLogQuery.maxParseErrors = 100;

// Create query text
var strQuery = "SELECT sc-bytes INTO C:\\output.csv FROM ex040528.log";

// Execute query
oLogQuery.ExecuteBatch(strQuery);

// Check if errors occurred
if( oLogQuery.lastError != 0 )
{
  WScript.Echo("Errors occurred!");

  var oMessages = new Enumerator( oLogQuery.errorMessages );
  for(; !oMessages.atEnd();  oMessages.moveNext())
  {
      WScript.Echo("Error message: " + oMessages.item());
  }

}
else
{
  WScript.Echo("Executed successfully!");
}

I don't really use vbscript or jscript, but for those that use it you can find the samples above and play with them to make it work for you.

Microsoft LogParser 2.2 Tutorial VI

Click here to see part V.

We have used logparser now in a variety of ways. We have used it to query logs and output them into different formats. We have used the datagrid and console which is default in logparser as well as .tpl files to get a nicely formatted html document. We also have used logparser from within powershell. Using the Interop (Interop.MSUtil.dll) assembly for logparser (TlbImp.exe LogParser.dll /out:Interop.MSUtil.dll) you can of course also call logparser from any .NET language. I wanted to start by showing a basic example on how this can be called from C#. In future guides, I will provide an easy to use library to call logparser from C#. Let's get to the examples then:

using IISW3CInputFormat = Interop.MSUtil.COMIISW3CInputContextClassClass;
using Interop.MSUtil;

       public ILogRecordset Excecute(string queryName)
       {
           //iis errors by hr. 
           string query = "SELECT TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)), COUNT(*) AS Error_Frequency FROM c:\logs\mylog.log WHERE sc-status >= 400 GROUP BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) ORDER BY TO_LOCALTIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) ASC";

           LogQueryClass logQuery = new LogQueryClassClass();
           IISW3CInputFormat oIISW3CInputFormat = new IISW3CInputFormat();
           return logQuery.Execute(query, oIISW3CInputFormat);
           //iterate through the ILogRecordSet so that you can work with each row
       }

Another example against the registry:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LogQuery = Interop.MSUtil.LogQueryClass;
using RegistryInputFormat = Interop.MSUtil.COMRegistryInputContextClass;
using RegRecordSet = Interop.MSUtil.ILogRecordset;

namespace ConsoleApplicationTest
{
   class Program
   {
       static void Main(string[] args)
       {
           {
               RegRecordSet rs = null;
               try
               {
                   LogQuery logQuery = new LogQuery();
                   RegistryInputFormat registryFormat = new RegistryInputFormat();
                   string query = @"SELECT Path from \HKLM\SOFTWARE\Microsoft where
                       Value='Microsoft'";
                   rs = logQuery.Execute(query, registryFormat);
                   for (; !rs.atEnd(); rs.moveNext())
                       Console.WriteLine(rs.getRecord().toNativeString(", "));
               }
               finally
               {
                   rs.close();
               }
           }
       }
   }
}

As you can see, using logparser from C# is very easy. The nicer apps would not hardcode a query but allow you to select a .sql file and a .log file to look at. I will show an example on how to do that next week as well as a core library which can be re-used for logparser.

Microsoft LogParser 2.2 Tutorial V

Click here to see part IV.

I wanted to take a little break before jumping into C# code and logparser to discuss template files. Template files allow a nicely formatted html style and some cases can be used for other things. OK, let's take a look now on how it works:

--select from eventlog
LogParser -i:EVT -o:TPL -tpl:c:\MyTPL.tpl "SELECT TimeGenerated, EventTypeName, SourceName, Message INTO MyLog.htm FROM \\MyServer\System WHERE EventTypeName = 'Error event' OR EventTypeName = 'Warning event'" -iCheckpoint:checkpoint.lpc

Most of this query you should already know. We are selecting warning or errors from the system event log. The switches include EVT for the event log, output of TPL and a .tpl file location. We are also including a checkpoint file to only look at events that have changed since the last time this query ran. Also, note that we are saving the results into an htm file.

Now we have the TPL file:

<l;pbody><l;table border="1">
<l;tbody><l;tr>
<l;td width="10%">%TimeGenerated%<l;/td>
<l;td width="10%"><l;span >%EventTypeName%<l;/span><l;/td>
<l;td width="10%">%SourceName%<l;/td>
<l;td width="70%">%Message%<l;/td>

<l;/tr>
<l;/tbody><l;/table><l;/lpbody>

All we are really doing with the tpl file is match the fields you selected in the query to the tpl file. The basic tpl files are simple html with just
<l;lp

An example I found online a while ago and can't seem to find the source again used tpl with AD. The idea was to use logparser to read a CSV and format it so that LDIF can then import the file. Here is the basic csv file created using csvde from Microsoft:

csvde -f c:\test.csv
-d ou=Users,dc=dev,dc=com
-r (objectclass=user)  -l dn,mail

You now have users and mail for some users in AD.

Now, create a template file:

<l;lpbody>dn: %FIELD_3%
changetype: modify
replace: userPrincipalName
userPrincipalName: %FIELD_4%
-<l;/lpbody>

If you now run a query to select against the csv and use the -tpl file you will get results in such a way that they can be imported into AD using LDIF. Personally, I have C# (or even powershell ways) to do mass imports into AD.

UPDATE: I finally found the post that showed me how to do this and it was on Scott Lowe's site.

Here is another great example on how to use TPL files.

In short, tpl is very nice since you can write a batch script to call logparser and create the tpl file and then do whatever you want with it such as FTPing it up to a site.

I'll get back to calling this from code tomorrow.

Microsoft LogParser 2.2 Tutorial IV

Click here to see part III.

In the past 3 tutorials, I talked a bit about using logparser straight from the console and querying various types of logs and CSV files. I have also shown how you can get various outputs of that data for example into a chart, csv, or just in the basic grid provided by logparser. In this next set of tutorials, I wanted to show how you can call logparser from code. The logparser API is actually not that bad and can be called in many ways and some examples even exist on the install (look at %%InstallFolder%%\Log Parser 2.2\Samples). I am first going to show you how to call logparser from Powershell. The good news is that a library exists for powershell and it is much easier to use the library then call it directly! So after you download the library, you can also create another script which can be called "LogParserUtils.ps1" (thanks to 'AC' for showing me this link from technet).

# http://blogs.technet.com/mscom/archive/2007/10/01/power-parsing-some-days-you-just-need-more-power-for-your-parser.aspx
function RecordsetToCVS($rs)
{
  $LPResult= new-object System.Management.Automation.PSObject[] 0

  while(!$rs.atEnd())
  {
      $rec = $rs.getRecord()
      $LPResult += new-Object System.Management.Automation.PSObject
    
      for($i = 0; $i -lt $rs.getColumnCount();$i++)
      {         
          $LPResult[$LPResult.length-1] | add-member NoteProperty $rs.getColumnName($i) -value $rec.getValue($i)
      }
    
      $rs.moveNext()
  }

  $rs.close()

  return $LPResult
}

Finally, it can be used like this:

function Test()
{      
      $query = @"
          SELECT *
          FROM \\MyServer\SYSTEM
          WHERE TimeWritten > TO_LOCALTIME( SUB( SYSTEM_TIMESTAMP(), TIMESTAMP('01:15', 'hh:mm') ) )
"@

      $inputformat = Get-LPInputFormat "EVT"
      RecordsetToCVS (Invoke-LPExecute $query $inputformat)

}

Test

There you go, calling logparser from powershell is now a snap! This reminds me, I mentioned in a previous post that I was going to compare logparser to powershell on speed from getting information to an event log. I'll try and post that this week.

Microsoft LogParser 2.2 Tutorial III

Click Here to see Part II.

In my next post about logparser, I wanted to discuss some usage in a few other formats as well as a few output options. The best way to learn this is by example, so here goes:

Query the Registry

--select from registry
LogParser -i:REG -o:datagrid "SELECT value, valuename from HKLM\Software\Microsoft WHERE ValueName='ProductID'"

Query AD
I would usually use C# or Powershell for this, but it can be done with logparser. Some complex examples can be found here.

Chart Output

--select unique visits and chart it
LogParser -i:IISW3C -o:chart -chartType:Line "SELECT date, count(c-ip) AS visitors INTO myChart.gif from c:\MyIISlog.log GROUP BY date"

--select asp errors by hour and chart it
LogParser -i:IISW3C -o:chart -chartType:Line "SELECT s-ip, TO_TIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) as timestamp, COUNT(*) AS Error_Frequency FROM c:\MyIISLog.log WHERE sc-status >= 400
AND (EXTRACT_EXTENSION(TO_LOWERCASE(cs-uri-stem)) IN ('asp';'aspx';'ashx';'ascx'))
GROUP BY s-ip, timestamp ORDER BY timestamp ASC"

--select percentage processing time by extension and chart it
LogParser -i:IISW3C -o:chart -chartType:Line "SELECT s-ip, EXTRACT_EXTENSION(cs-uri-stem) AS Extension, TO_INT(MUL(PROPSUM(time-taken),100.0)) AS Processing_Time
FROM c:\MyIISLog.log GROUP BY s-ip, Extension ORDER BY Processing_Time DESC"

Dump into SQL Server

--dump into sql server
LogParser -i:IISW3C "SELECT * INTO WebLogs from c:\MyIIS.log" -server:MyServer\SQLExpress -database:IISLogs -driver:"SQL Server" -createTable:ON

One note for sql server is that you can create the table manually and then import just the fields you want or all of them. If you use createTable:ON then it will make a new table for you in the db (make sure you have rights). The default column length though is 255 and data might get truncated, so I usually create my own table first.

You now have a bunch of examples on input and forensics for logparser and output formats as well. I have just scratched the surface with input/output though.

Next week I'll go into some examples on how to call logparser from code.

Microsoft LogParser 2.2 Tutorial I

One of my favorite tools that I use all the time is logparser. This tool is well known among many sys admins, but not as well known in the development world. To quote from Microsoft "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®." Basically, it is a tool that can query anything! Some things I have used it for are IIS, Log4Net, FileMon, Exchange, Event, and Akamai logs. I have also used it to create charts and CSV files from these files.

I wanted to start a tutorial that would give insight into this great tool and will show you how to use it. I will also show how you can call it from C# and powershell and some great libraries to use.

LogParser is a console based application but there are some people who created various UI's for it. You can write the sql directly or as I like to do it store it in a .sql file and call it like this:

LOGPARSER -i:IISW3C file:C:\MyLogParserFile.sql -o:DataGrid

There are tons of switches that can be added, and we will go through this in in further articles but note that -i stands for input. Although logparser can usually figure out the format, you should try and tell it what format the file is. In this case, I wanted it to know that the file is an IIS log file. The -o stands for output and I wanted it to show the output in a datagrid. If you leave the -o out it will display it in the default format which is in the console. You can try logparser -h and it will give you some more details.

I wanted to start off by showing some basic queries against IIS logs.

CountClients.sql

select count(*) from c:\MyIIS.log

Top10LongestTimeTakenPagesOnAverage.sql

SELECT
top 10 TO_LOWERCASE(cs-uri-stem),
cs-uri-query,
TO_LOCALTIME(TO_TIMESTAMP(date, time)) as timestamp,
sc-bytes,
time-taken

FROM
c:\MyIIS.Log

ORDER BY
time-taken DESC

Top25MostPopularPages.sql

SELECT
top 25 cs-uri-stem,
count(cs-uri-stem) As Hits
FROM c:\MyIIS.log

GROUP BY
cs-uri-stem

ORDER BY
 count(cs-uri-stem) DESC

CountUniqueClients.sql

SELECT count(distinct c-ip) from c:\MyIIS.log

Please let me know if you had any specifics you would like me to discuss.

LogParser and Powershell

Here is a great library that can be used to call logparser from powershell. The cmdlets are written for you to use and are very easy to follow.

Powershell and Event Logs

The Powershell blog posted an article on how easy it is to read the event log. I have used and continue to use logparser from powershell to read the event log as I always have though logparser would be faster. Could I be wrong? I'll run a test tonight and try and get the results in to you tomorrow. What do you you use to parse the event log in windows?