Exchange DB Reporting with PowerShell and Log Parser

I ran across a useful post today as I was roaming through Google Analytics.

Using PowerShell, LogParser and PowerGadgets to get Exchange 2003 storage information – Part 1

Wes Stahler uses Log Parser and PowerShell to report on the free space in an Exchange Database.

This is a task I have done in the past. I will add this script to my toolkit.

Regards,

Dave

PowerShell, Log Parser, PowerGadgets, and GeoIP what fun!

Welcome!

One day I was pondering how I might use log parser to map visitors to a website by state. I am aware this is easily done with tools like Google Analytics, but I was interested in using existing logs for the info.

Using the PowerShell and Log Parser functions from the library listed in a previouse post. Log parser can easily get the visitors by IP Address from an IIS log.

The next task is to get the location of the IP addresses. The tool I chose for this task was the free GeoLite City from MaxMind http://www.maxmind.com/app/geolitecity. Here is an example:

There are a couple of ways to use the MaxMind GeoIP database. It can be used in it’s native binary format or it can be imported into SQL from csv files. MaxMind recommends using the binary format, which is what I chose to do. MaxMind also provides API’s for use with a variety of platform’s. I chose to use the COM version.

After the location is determined the counts are calculated. This brings us to the point where we need to chart the results. The tool I chose for this operation is PowerGadgets. This is a charting tool made for use with PowerShell, it can be handy. Here is an example:

 

And here is our final Chart.

 

This works pretty well the only drawback to this solution is that PowerGadgets is a pay tool, but since I own a copy it suites my needs.

Regards,

Dave

Log Parser and PowerShell – Part II

Welcome back!

Last post I talked about using the Log Parser executable from PowerShell. I also briefly mentioned the Log Parser COM component. In this post I will go into more depth on using the COM component from PowerShell.

The COM component exposes a simple object model consisting of only three main objects.

  • LogQuery – Object used to execute queries and batches
  • LogRecordSet – Object returned by LogQuery.Execute method
  • LogRecord – Child object of LogRecordset. Object returned by LogRecordSet.getRecord method

There are also objects containing the input and output formats. There are several of them and they are well documented in the Log Parser documentation. I will give examples as we progress.

The first step on my task to integrate with the Log Parser COM object was to put together a function library of the basic building blocks.

The library consists of the following functions.

  • Get-LPInputFormat – Returns a Log Parser input format object
  • Get-LPOutputFormat – Returns a Log Parser output format object
  • Invoke-LPExecute – Executes the LogQuery.Execute method and returns a LogRecordSet
  • Invoke-LPExecuteBatch – Executes the LogQuery.ExecuteBatch method to output the query in the requested format
  • Get-LPRecordSet – Executes a Log Parser Query and returns a LogRecordSet as an array of PowerShell objects
  • Get-LPRecord – Returns LogRecord object as a PowerShell object from the current record of a LogRecordSet object

With these functions we can support almost all of the Log Parser functionality in PowerShell. I did not build in support for the custom COM input type or the NAT and Datagrid output types. The NAT and Datagrid output types can be handled in a different way in PowerShell. The COM input format is a challenge I left for another day.

Here is the function library.

This library provides the basic functionality needed for Log Parser. We can use it in two basic scenarios.

One – We want to execute a Log Parser batch. This mode works exactly as the Log Parser command line tool works it queries an input file of a given type and writes the results to an output file of a given type.

Two – We want the results of a Log Parser query returned to a PowerShell object. This will allow us to further process the results using PowerShell or simply utilize the output facilities to display the results.

Example of Scenario One:

The code above provides a good way to create scheduled reports. The syntax is easier to follow than the command line switches at least for me.

Here is the sample output chart.

Yes, I took this log from a box with nothing but hacker traffic J

Now let’s look at the second scenario. We will return the results of a Log Parser query in a PSObject.

Here we are querying for application hang events in the application event log. We will use a Log Parser query to retrieve just the events we want. Then we will use PowerShell to filter out just the events for IE. The we can easily display the output any way we like.

Here is the output.

 

To me this really seems like the best of both worlds utilizing each tool for its strength.

Now I just have to figure out why IE hangs J

Best Regards,

Dave

Log Parser and PowerShell – Part I

Welcome!

Log Parser and PowerShell are both great tools and they work well when used together. Yes, you can do pretty much everything Log Parser does with PowerShell alone, but part of PowerShell’s mission is to better leverage current tools. I believe this is an excellent example. Also it has been my experience Log Parser performs better at the task. Steve Schofield also blogged about the performance of Log Parser and PowerShell for querying logs here.

There are two ways to interact with Log Parser from PowerShell. The first and the easiest to get started with is the command line version logparser.exe.

Here is the same command line query example from the last post but with PowerShell.

& ./logparser.exe “SELECT Top 10 cs-uri-stem, Count(*) FROM D:Logsex081110.log Group By cs-uri-stem Order by cs-uri-stem desc” –i:w3c

Big difference isn’t it. J

Here is a simple powershell script using Log Parser. This is a little easier to follow than the batch file example in the last post.

The drawback to these examples is we are not really gaining the full benefit from PowerShell. We could write some functions in PowerShell and convert the text output from Log Parser into PowerShell objects, but there is an easier way to do this.

OK, now for the second way to interact with Log Parser from PowerShell, the Log Parser COM component (logparser.dll). This component installs with Log Parser and should be registered and ready to use if you have installed Log Parser.

The COM component exposes a simple object model consisting of only three main objects. The LogQuery, LogRecorset, and LogRecord objects. There are also a series of objects for the input and output formats.

The COM component is more difficult to use, but is an advantage because data can be returned in object form. We will look at this in detail starting in Log Parser and PowerShell – Part II

Best Regards,

Dave

Log Parser Basics

Welcome!

Log Parser is a great tool for analyzing many types of text data. It is a free downloadable tool from Microsoft it installs a command line version and a COM component. It is a powerful tool for using the SQL language to query most types of fixed or delimited text data.

I recommend this article as an excellent place to begin learning to use log parser. It was written by the author of Log Parser, Gabriele Giuseppini and is a good overview. After reading it a great next step is to download Log Parser 2.2 here install it and take a look at the compiled help file.

The help file for the product is pretty good. The first two sections have enough information to get a good start. General use of Log Parser is covered there and other places so I will only give a brief overview here.

I believe most people probably start using log parser directly on the command line.

Example: c:LogParserlogparser.exe “SELECT Top 10 cs-uri-stem, Count(*) FROM D:Logsex081110.log Group By cs-uri-stem Order by cs-uri-stem desc” –i:w3c

Another good way to experiment with Log Parser is to use a batch file and a SQL file.

Example – Top 10 web Pages:

Batch File

This batch file prompts for the input, output, and sql query files and executes Log Parser to query a w3c log file. Log Parser allows the use of variables inside the SQL query. The example below inserts the values for the input and output files entered at the prompts into the query.

 

Log Parser SQL File

This SQL file gets the top ten pages by hits from a w3c log file.

These are basic examples of how Log Parser works. Next time I will talk about using Log Parser from PowerShell.

Best Regards,

Dave