David Muegge’s Blog

IT Eclectia - A variety technical topics and tips.

Log Parser and PowerShell – Part II

November 21st, 2008tagsLog Parser, PowerShell userdmuegge

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194

 
# ---------------------------------------------------------------------------
### <Library name='Muegge_LogParser_Lib.ps1'>
### <Author>David Muegge</Author>
### <CreateDate>20081108</CreateDate>
### <ModifiedDate>20081121</ModifiedDate>
### <Description>
### 	Log Parser function library
### </Description>
### <Dependencies>
###		Log Parser 2.2 COM component
### </Dependencies>
### <Usage>
### 	Dot source from calling script
### </Usage>
### </Library>
# ---------------------------------------------------------------------------
 
# ---------------------------------------------------------------------------
### <Function name='Get-LPInputFormat'>
### <Description>
### 	Returns Log Parser Input Format object based on passed string
### </Description>
### <Usage>
###		Get-LPInputFormat -InputType <string>
### </Usage>
### </Function>
# ---------------------------------------------------------------------------
function Get-LPInputFormat{
 
	param([String]$InputType)
 
	switch($InputType.ToLower()){
		"ads"{$inputobj = New-Object -comObject MSUtil.LogQuery.ADSInputFormat}
		"bin"{$inputobj = New-Object -comObject MSUtil.LogQuery.IISBINInputFormat}
		"csv"{$inputobj = New-Object -comObject MSUtil.LogQuery.CSVInputFormat}
		"etw"{$inputobj = New-Object -comObject MSUtil.LogQuery.ETWInputFormat}
		"evt"{$inputobj = New-Object -comObject MSUtil.LogQuery.EventLogInputFormat}
		"fs"{$inputobj = New-Object -comObject MSUtil.LogQuery.FileSystemInputFormat}
		"httperr"{$inputobj = New-Object -comObject MSUtil.LogQuery.HttpErrorInputFormat}
		"iis"{$inputobj = New-Object -comObject MSUtil.LogQuery.IISIISInputFormat}
		"iisodbc"{$inputobj = New-Object -comObject MSUtil.LogQuery.IISODBCInputFormat}
		"ncsa"{$inputobj = New-Object -comObject MSUtil.LogQuery.IISNCSAInputFormat}
		"netmon"{$inputobj = New-Object -comObject MSUtil.LogQuery.NetMonInputFormat}
		"reg"{$inputobj = New-Object -comObject MSUtil.LogQuery.RegistryInputFormat}
		"textline"{$inputobj = New-Object -comObject MSUtil.LogQuery.TextLineInputFormat}
		"textword"{$inputobj = New-Object -comObject MSUtil.LogQuery.TextWordInputFormat}
		"tsv"{$inputobj = New-Object -comObject MSUtil.LogQuery.TSVInputFormat}
		"urlscan"{$inputobj = New-Object -comObject MSUtil.LogQuery.URLScanLogInputFormat}
		"w3c"{$inputobj = New-Object -comObject MSUtil.LogQuery.W3CInputFormat}
		"xml"{$inputobj = New-Object -comObject MSUtil.LogQuery.XMLInputFormat}
 
	}
 
	return $inputobj
 
}
 
 
# ---------------------------------------------------------------------------
### <Function name='Get-LPOutputFormat'>
### <Description>
### 	Returns Log Parser Output Format object based on passed string
### </Description>
### <Usage>
###		Get-LPOutputFormat -OutputType <string>
### </Usage>
### </Function>
# ---------------------------------------------------------------------------
function Get-LPOutputFormat{
 
	param([String]$OutputType)
 
	switch($OutputType.ToLower()){
		"csv"{$outputobj = New-Object -comObject MSUtil.LogQuery.CSVOutputFormat}
		"chart"{$outputobj = New-Object -comObject MSUtil.LogQuery.ChartOutputFormat}
		"iis"{$outputobj = New-Object -comObject MSUtil.LogQuery.IISOutputFormat}
		"sql"{$outputobj = New-Object -comObject MSUtil.LogQuery.SQLOutputFormat}
		"syslog"{$outputobj = New-Object -comObject MSUtil.LogQuery.SYSLOGOutputFormat}
		"tsv"{$outputobj = New-Object -comObject MSUtil.LogQuery.TSVOutputFormat}
		"w3c"{$outputobj = New-Object -comObject MSUtil.LogQuery.W3COutputFormat}
		"tpl"{$outputobj = New-Object -comObject MSUtil.LogQuery.TemplateOutputFormat}
 
	}
 
	return $outputobj
 
}
 
 
# ---------------------------------------------------------------------------
### <Function name='Invoke-LPExecute'>
### <Description>
### 	Executes a Log Parser Query and returns a recordset
### </Description>
### <Usage>
###		Invoke-LPExecute -query <string>
### </Usage>
### </Function>
# ---------------------------------------------------------------------------
function Invoke-LPExecute{
 
	param([string] $query, $inputtype)
 
    $LPQuery = new-object -com MSUtil.LogQuery
	if($inputtype){
    	$LPRecordSet = $LPQuery.Execute($query, $inputtype)	
	}
	else
	{
		$LPRecordSet = $LPQuery.Execute($query)
	}
    return $LPRecordSet
 
}
 
 
# ---------------------------------------------------------------------------
### <Function name='Invoke-LPExecuteBatch'>
### <Description>
### 	Executes Log Parser batch query with passed input and output types
### </Description>
### <Usage>
###		Invoke-LPExecuteBatch -query <string> -inputtype <LogParserInputFormat> -outputtype <LogParserOutputFormat>
### </Usage>
### </Function>
# ---------------------------------------------------------------------------
function Invoke-LPExecuteBatch{
 
	param([string]$query, $inputtype, $outputtype)
 
    $LPQuery = new-object -com MSUtil.LogQuery
    $result = $LPQuery.ExecuteBatch($query, $inputtype, $outputtype)
    return $result
}
 
 
# ---------------------------------------------------------------------------
### <Function name='Get-LPRecord'>
### <Description>
###		Returns PowerShell custom object from Log Parser recordset for current record
### </Description>
### <Usage>
###		Get-LPRecord -rs <RecordSet>
### </Usage>
### </Function>
# ---------------------------------------------------------------------------
function Get-LPRecord{
 
	param($LPRecordSet)
 
	$LPRecord = new-Object System.Management.Automation.PSObject
	if( -not $LPRecordSet.atEnd())
	{
		$Record = $LPRecordSet.getRecord()
		for($i = 0; $i -lt $LPRecordSet.getColumnCount();$i++)
		{        
			$LPRecord | add-member NoteProperty $LPRecordSet.getColumnName($i) -value $Record.getValue($i)
		}
	}
	return $LPRecord
}
 
 
 
# ---------------------------------------------------------------------------
### <Function name='Get-LPRecordSet'>
### <Description>
### 	Executes a Log Parser Query and returns a LogRecordSet as a custom powershell object
### </Description>
### <Usage>
###		Get-LPRecordSet -query <string>
### </Usage>
### </Function>
# ---------------------------------------------------------------------------
function Get-LPRecordSet{
 
	param([string]$query)
 
	# Execute Query
	$LPRecordSet = Invoke-LPExecute $query
	$LPRecords = new-object System.Management.Automation.PSObject[] 0
	for(; -not $LPRecordSet.atEnd(); $LPRecordSet.moveNext())
	{
		# Add record
		$LPRecord = Get-LPRecord($LPRecordSet)
		$LPRecords += new-Object System.Management.Automation.PSObject	
        $RecordCount = $LPQueryResult.length-1
        $LPRecords[$RecordCount] = $LPRecord
	}
	$LPRecordSet.Close();
	return $LPRecords
 
}

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: 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

 
. .\Muegge_LogParser_Lib.ps1
 
$infile = "D:\Temp\LPTest.log"
$outfile = "D:\Temp\LPTest.gif"
 
# Set input format and parameters
$inputformat = Get-LPInputFormat "w3c"
 
# Set output format and parameters
$outputformat = Get-LPOutputFormat "chart"
$outputformat.chartTitle = "Top 10 Pages"
$outputformat.chartType = "BarStacked"
$outputformat.groupSize = "600x400"
 
$query = @"
SELECT Top 10 cs-uri-stem, Count(*)
INTO $outfile
FROM $infile
Group By cs-uri-stem
Order by cs-uri-stem desc	
"@
 
# Execute batch
Invoke-LPExecuteBatch $query $inputformat $outputformat | Out-Null

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. 

1
2
3
4
5
6
7
8
9
10
11
12

 
. .\Muegge_LogParser_Lib.ps1
 
$query = @"
SELECT *
FROM Application
WHERE EventID = 1002
"@
 
$inputformat = Get-LPInputFormat "evt"
$records = Get-LPRecordSet $query $inputformat
$records | where {$_.Strings -like "*iexplore.exe*"} | ft -property TimeGenerated,SourceName,Strings

Here is the output.

TimeGenerated				SourceName   			Strings
-------------     				----------       			-------
11/1/2008 11:21:02 PM			Application Hang			iexplore.exe|7.0.6000.16735|hungapp|0.0...
11/2/2008 6:25:35 PM			Application Hang			iexplore.exe|7.0.6000.16735|hungapp|0.0...
11/4/2008 7:32:35 AM			Application Hang			iexplore.exe|7.0.6000.16735|hungapp|0.0...
11/6/2008 4:21:22 PM			Application Hang			iexplore.exe|7.0.6000.16735|hungapp|0.0...
11/8/2008 7:05:20 PM			Application Hang			iexplore.exe|7.0.6000.16735|hungapp|0.0...
11/10/2008 3:01:45 PM			Application Hang			iexplore.exe|7.0.6000.16735|hungapp|0.0...
11/11/2008 5:10:40 PM			Application Hang			iexplore.exe|7.0.6000.16735|hungapp|0.0...
11/14/2008 12:13:06 PM			Application Hang			iexplore.exe|7.0.6000.16735|hungapp|0.0...
11/17/2008 7:34:29 AM			Application Hang			iexplore.exe|7.0.6000.16735|hungapp|0.0...

 

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

9 Comments

rssComments RSS transmitTrackBack Identifier URI


Hi Dave,

I’m really impressed with your Library. Basicly it supports extact the Features that i need. Thanks it safed me alot of Time.

For a personal Project i added the following Method to the Library.

# —————————————————————————
###
###
### Creates a Table Object from a LogRecordSet
###
###
### Get-LPTableResults -query -inputtype
###
###
# —————————————————————————
function Get-LPTableResults
{
param([string] $query, $inputtype)

$rsLP = Invoke-LPExecute $query $inputtype
$tab = new-object System.Data.DataTable(”Results”)

$i = 0

for($i = 0; $i -lt $rsLP.getColumnCount();$i++)
{
$col = new-object System.Data.DataColumn;
$ct = $rsLP.getColumnType($i)
$col.ColumnName = $rsLP.getColumnName($i);
switch ($rsLP.getColumnType($i))
{
“1″
{
$col.DataType = [System.Type]::GetType(”System.Int32″);
break;
}

“2″
{
$col.DataType = [System.Type]::GetType(”System.Double”);
break;
}

“4″
{
$col.DataType = [System.Type]::GetType(”System.DateTime”);
break;
}

default
{
$col.DataType = [System.Type]::GetType(”System.String”);
break;
}

}

$tab.Columns.Add($col);
}

$i = 0
while(-not $rsLP.atEnd())
#for(; -not $rsLP.atEnd(); $rsLP.moveNext())
{
$rowLP = $rsLP.getRecord();
$row = $tab.NewRow();
for ($i = 0; ($i -lt $rsLP.getColumnCount()); ($i++))
{
$columnName = $rsLP.getColumnName($i)
$row.$columnName = $rowLP.getValue($i);
}
$tab.Rows.Add($row);
$rsLP.moveNext();
}

$ds = new-object System.Data.DataSet
$ds.Tables.Add($tab)
return $ds.Tables["Results"]
}

Maybe it for others usefull to.

Best Regards Ivo

Comment by Ivo Looser on January 21, 2009 3:05 am


Ivo,

I am glad you found it useful!

Thanks for the addition.

Dave

Comment by admin on January 22, 2009 8:38 pm


Great post! I referenced it on my blog at http://fatbeards.blogspot.com/2009/02/powershelllog-parser-22-and-add-member.html

Comment by Wes Stahler on February 16, 2009 5:42 pm


Thx much for the contribution…would be very interesting to see the additions and refinements since the original post. What sorts of lessons have you learned since?

Comment by steve on April 10, 2009 7:02 am


This looks great. one question. I would love to use PowerShell and LogParser to grab the Event Logs from my domain controllers and send them to a SQL server for further analysis. How would you recommend doing something like that? I am just getting started with PowerShell and LogParser so any assistance would be greatly appreciated.

Thanks

app

Comment by Aaron on June 5, 2009 10:43 am


Aaron,
Thanks for the feedback.

Log Parser has the ability to output data to SQL inherently. This is done via the SQL output format. The Log Parser help file has information and examples under the output formats section.

To use my library to do this in PowerShell you would do something like this:

. .\Muegge_LogParser_Lib.ps1

$query = @”
SELECT *
FROM Application
WHERE EventID = 1002
“@

$inputformat = Get-LPInputFormat “evt”
$outputformat = Get-LPOutputFormat “sql”
$outputformat.server = “SVRNAME”
$outputformat.database = “DBNAME”
$outputformat.username = “USRNAME”
$outputformat.password = “PASSWORD”
Invoke-LPExecuteBatch $query $inputformat $outputformat

You could also just return objects and use a PowerShell library such as SQLPSX ( http://sqlpsx.codeplex.com ) to add the data to SQL. This would give a greater degree of control but would also be more complex.

Regards,

Dave

Comment by dmuegge on June 10, 2009 7:44 am


Hi Dave,

I’m new to PowerShell and your library was exactly what I needed. This library is awesome, I was able to implement your first scenario on my own data [input:TSV file, output:TSV file] (I’m doing this against SharePoint UML log files in TSV format). Commands below:

$infile = “C:\abc\Macine1-*.log”
$outfile = “C:\abc\My_Error_Results.tsv”
$inputformat = Get-LPInputFormat “tsv”
$outputformat = Get-LPOutputFormat “tsv”

SELECT Timestamp, Area, Category, Level, Message
INTO $outfile
FROM $infile
Where Message LIKE ‘%abc%’
ORDER BY Timestamp

Invoke-LPExecuteBatch $query $inputformat $outputformat | Out-Null

Although not very useful, ultra cool :).

However, when I tried to implement your second scenario on my own data [input:TSV file, output:PSObject], I always get exceptions stating that the columns that I asked for in the query do not exist. Commands below:

$infile = “C:\abc\Machine1-*.log”
$inputformat = Get-LPInputFormat “tsv”

$query2 = @”
SELECT Timestamp, Area, Category, Level, Message
FROM $infile
Where Message LIKE ‘%abc%’
ORDER BY Timestamp
“@

$records = Get-LPRecordSet $query2 $inputformat —Exception Occurs Here
$records | where{$_.Message -like “*bsc*”}

I’m sure i’m missing something increadably simple, please help…:)

Comment by Ptolemy02 on July 30, 2009 5:32 pm


How do you include the -checkpoint value into the Invoke-LPExcute command in the Powershell script if you want to use your logparser library to monitor a log file so it only queries the new log file lines since the last iterration?

I tried:
# Execute batch
Invoke-LPExecuteBatch $query $inputformat -checkpoint:
but this is not working.

Here is my PS code snippet:

$infile =
$checkpoint =
$query = @”
Select Text FROM $infile WHERE Text LIKE ‘%ERROR:%’
“@
$inputformat = Get-LPInputFormat “TEXTLINE”
Invoke-LPExecute $query $inputformat -checkpoint:$checkpoint

Script runs fine but no check file is created

Comment by George on December 10, 2009 9:25 pm


This saved me a lot of time on a recent project that required some log-digging across multiple servers. Now I have something reusable and clean enough that other folks will be able to make sense of it down the line. Thanks a lot, great stuff.

Comment by AJ on April 6, 2010 4:06 pm

addLeave a comment






The previous post is Log Parser and PowerShell – Part I and the next one is Automated Machine Builds with PowerShell and AutoIT