다음을 통해 공유

Power Parsing...some days you just need more power for your parser

  • 아티클

Obviously the title can mean a bunch of different things, in this case we are merging two fun technologies: Microsoft PowerShell and Microsoft Log Parser. Here in MSCOM Operations, we are in the process of introducing Microsoft PowerShell into our environment and as a serious user of Log Parser I wondered how to get the two to interact nicely, hopefully in a way that makes each a little more valuable.

The first solution was to simply call logparser.exe from the PowerShell . This did what I expected, returned a bunch of lines to the window... not real exciting, but at least we determined that you can do it. Looking for something better, I stumbled across the fact that PowerShell can manipulate COM objects directly. Great, we can use Log Parser's COM object model from PowerShell pretty much exactly like it has always been used from script.

I started with some simple JScript:

//Create the main Log Parser Query object

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

//Create the text of the query

var szQuery = "Select top 10 * from r:\ex07011210.log";

var recordSet=myQuery.Execute(szQuery);

for(; !recordSet.atEnd(); recordSet.moveNext())

{

            record=recordSet.getRecord();

            WScript.Echo(record.GetValue(0) + ","+record.GetValue(1));

}

recordSet.Close();

Which I ported to PowerShell :

$myQuery = new-object -com MSUtil.LogQuery

$szQuery = "Select top 10 * from r:\ex07011210.log";

$recordSet = $myQuery.Execute($szQuery)

for(; !$recordSet.atEnd(); $recordSet.moveNext())

{

            $record=$recordSet.getRecord();

            write-host ($record.GetValue(0) + ","+ $record.GetValue(1));

}

$recordSet.Close();

Great! Easy! No Problems! Of course we really haven't added much value, and accessing $rec.getValue(0) seems so ugly. The next big breakthrough in investigating the integration was finding the PowerShell command import-csv. We could now have Log Parser output to CSV and then use import-csv to suck the data back into PowerShell :

PSv1.0 R:\> LogParser "select top 10 * into temp.csv from r:\ex07011210.log"

Statistics:

-----------

Elements processed: 10

Elements output: 10

Execution time: 0.05 seconds

PSv1.0 R:\> $recordSet = import-csv temp.csv

PSv1.0 R:\> $recordSet[0].LogFileName

r:\ex07011210.log

PSv1.0 R:\> $recordSet[0].LogRow

5

PSv1.0 R:\> $recordSet[1].LogRow

6

This is nifty because import-csv creates objects that have the field names as properties... VERY cool. Of course there is the minor drawback of creating a temp.csv file (and possibly conflicting over it, leaking it, etc). I Started thinking, "since import-csv can dynamically generate objects, I bet I can do the same", and after some internet searching I was able to produce:

function LPExecute([string] $query)

{

            $lp = new-object -com MSUtil.LogQuery

            $rs = $lp.Execute($query)

            $LPResult= new-object System.Management.Automation.PSObject[] 0;

            if(!$rs.atEnd())

            {

                        do {

                                    $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();

                        } until ($rs.atEnd())

            }

            $rs.close();

            return $LPResult;

}

And after adding it to my profile I can do stuff like:

PSv1.0 R:\> $recordSet = LPExecute("select top 10 * from r:\ex07011210.log")

PSv1.0 R:\> $recordSet[0].LogFileName

r:\ex07011210.log

PSv1.0 R:\> $recordSet[0].LogRow

5

PSv1.0 R:\> $recordSet[1].LogRow

6

Without the use of a temp.csv (though I fully admit it might be a little faster to have one - I haven't bothered to find out). The important piece of this puzzle is the use of the System.Management.Automation.PSOject, and its add-member functionality. This is what allows us to create dynamically extensible objects (something JScript does by default).

To round things out (and come around full circle), I thought someone might want to walk through the record set instead of getting back the full array, so I added the functions to allow you to do it this way as well:

function LPExecuteOnly([string] $query)

{

            $lp = new-object -com MSUtil.LogQuery

            $rs = $lp.Execute($query)

            return $rs

}

function LPGetRecord ($rs)

{

            $LPR = new-Object System.Management.Automation.PSObject;

            if(!$rs.atEnd())

            {

                        $rec = $rs.getRecord();

                        for($i = 0; $i -lt $rs.getColumnCount();$i++)

                        {

                                    $LPR | add-member NoteProperty $rs.getColumnName($i) -value $rec.getValue($i);

                        }

            }

            return $LPR;

}

With these functions we can do something that looks a lot like our original, but with interesting properties instead of calls to GetValue():

$recordSet = LPExecuteOnly("select top 10 * from r:\ex07011210.log")

for(; !$recordSet.atEnd(); $recordSet.moveNext())

{

        $record=LPGetRecord($recordSet);

        write-host ($record.LogFileName + ","+ $record.LogRow);

}

$recordSet.Close();

So there you have it. Simply copy those functions into your profile and you can access Log Parser in a clean, nifty way (assuming Log Parser is registered properly). Obviously we haven't added a lot of impact over Log Parser + Script (got rid of (GetValue(#) calls), however for those of you interested in and already using Microsoft PowerShell these could be a good functions to have in your toolbox.

At some point I may have to investigate connecting the two going the other direction, by creating a Log Parser custom input type using PowerShell ... assuming I find something that Log Parser can't already parse.

Have fun Power Parsing !

Comments