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!

Project Type not supported by this installation!

I got the following error recently when opening up a project file in Visual Studio 2008: "The project type is not supported by this installation..."

Hmmm, it works on one machine and not the other and I have no idea why. The solution is to go to the Visual Studio command prompt and run: devenv /setup

Now the project seems to open up fine. I am still not sure why this happened.

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.

Powershell and Includes

When I work with Powershell I always break out files into multiple .ps1 files. The way I include them looks like this:

# Current script path
$RUN_DIR = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent

# References
Add-Type -Assembly System.Configuration
[System.Reflection.Assembly]::LoadFrom($RUN_DIR + "\lib\ICSharpCode.SharpZipLib.dll")

# Includes
. ($RUN_DIR + '\inc\Common.ps1')
. ($RUN_DIR + '\inc\PathUtils.ps1')
. ($RUN_DIR + '\inc\PathHelper.ps1')

Import-Module ($MY_DIR + '\inc\ArchiveUtils.psm1')

. ($RUN_DIR + '\configs\general_settings.ps1')
. ($RUN_DIR + '\configs\mail_settings.ps1')

Most people have of course used Add-Type and Import-Module. However, what about including others .ps1 files? The above works well for me. How do you do it?

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.

Set IIS Settings using Powershell

I actually came across this yesterday. I figured I'd share this with people since it is kind of interesting.