PowerShell, Get-Winevent returns a blank column for ‘Message’ property

As part of process to verify event logging across a Windows Infrastructure, came across an odd Powershell query issue. I had hoped to leverage PowerShell to mass convert a folder worth of archived logs (i.e. 1GB/file) into one or more .csv file(s). After fifteen minutes, I had composed a simple query for the conversion:

Get-Winevent -path “c:\logs\*.evtx” | select-object -property TimeCreated, Message | export-csv “C:\garzafx\logs.csv”

Get windows logs from this folder, get “time created” plus “message body”, and export to .csv file to view in Excel.

Initial conversions had been successful. I had scheduled to run the full set of files a few days later. Revisiting this process, the message field had stopped providing data. It had started to return blank column. So between forums I had executed the same query with same log files in the following scenarios:

1. Loaded Windows 2008 R2 and associated patches.

2. Upgraded to Windows Server 2012 from 2008 R2.

3. Clean install of Windows Server 2012, no windows updates.

4. Freshly reimaged system running Windows 7 from Service Desk.

5. Upgraded to Powershell 4.0 beta.

6. Switched to PowerShell –Version 2 switch.

7. Applied US English language 3rd party PowerShell custom script with my query nested.

All actions had resulted in the same blank column for ‘Message’ property. Saving out logs manually had given me the following prompt and hope.

20130729-215253.jpg

However, the originating server had provided the same empty results. Reading a few more blogs, I had read up again on Log Parser Studio 2.0. This application had provided something new, a mix of SQL and Powershell commands.

8. After launching LPS 2.0,  I had selected the folder for my logs (i.e. C:\logs) with this icon  20130729-221138.jpg from the following window.

20130729-215329.jpg

9.   Updated the SELECT statement to use STRING for message property with TOP 10 statement provided by LPS 2.0. The following was successfully returned for ‘Message’ property.

20130729-215337.jpg

10. After dropping the TOP 10 reference,  I had pressed the following 20130729-221129.jpg button to export the T-SQL commands to get a PowerShell script.

20130729-215320.jpg

11. After LPS 2.0 exporting myscripts.ps1, I had executed Set-ExecutionPolicy RemoteSigned at prompt.  Finally, I had been able to  convert .evtx files into .csv files with the ‘Message’ column full. Below was example of the original script. In the end, Log Parser Studio 2.0 was a great find for those looking for another means of generating PowerShell scripts. As for the original query not working, some other bloggers have pushed the notion of a software bug.

Example of output:

Install Log Parser Studio

##################################

# Generated by Log Parser Studio #

##################################

#Name: STRINGS

#Log Type: EVTLOG

#Generated: 7/25/2013 7:14:54 AM

Param

(

[parameter(Mandatory=$false,ValueFromPipeline=$true)]

[Bool]$AutoOpen,

[parameter(Mandatory=$false,ValueFromPipeline=$true)]

[String]$OutFile,

[parameter(Mandatory=$false,ValueFromPipeline=$true)]

[Bool]$IgnoreInParams,

[parameter(Mandatory=$false,ValueFromPipeline=$true)]

[Bool]$IgnoreOutParams)

$Error

.Clear()

$DefaultFolder

=[Environment]::GetFolderPath(“MyDocuments”)

$Destination

=“myeventlog.csv”

$Destination

=$DefaultFolder+“\”+$Destination

if

($OutFile-ne[String]::Empty)

{

$OutFileType=[System.IO.Path]::GetExtension($OutFile.ToUpper())

$OriginalFileType=[System.IO.Path]::GetExtension($Destination.ToUpper())

if($OutFileType-ne$OriginalFileType)

{

Write-Host“You have chosen”$OutFileType“as the output, but this script was originally generated as”$OriginalFileType-ForegroundColorRed

Write-Host“Either change -OutFile to”$OriginalFileType“or generate the script again with the output as”$OutFileType-ForegroundColorRed

Write-Host“You can also modify the OutputFormat variable in this script to match the correct Log Parser 2.2 COM output format.”-ForegroundColorRed

[System.Environment]::NewLine

return

}

else

{

if($true-ne$OutFile.Contains(“\”))

{

$Destination=$DefaultFolder+“\”+$OutFile

}

else

{

$Destination=$OutFile

}

}

}

$LogQuery

=New-Object-ComObject“MSUtil.LogQuery”

$InputFormat

=New-Object-ComObject“MSUtil.LogQuery.EventLogInputFormat”

$OutputFormat

=New-Object-ComObject“MSUtil.LogQuery.CSVOutputFormat”

if

($IgnoreInParams-eq$false){

$InputFormat.fullText=1

$InputFormat.resolveSIDs=0

$InputFormat.formatMsg=1

$InputFormat.msgErrorMode=“MSG”

$InputFormat.fullEventCode=0

$InputFormat.direction=“FW”

$InputFormat.stringsSep=“|”

$InputFormat.binaryFormat=“PRINT”

$InputFormat.ignoreMessageErrors=1

}

if

($IgnoreOutParams-eq$false){

$OutputFormat.Headers=“AUTO”

$OutputFormat.oDQuotes=“AUTO”

$OutputFormat.tabs=“OFF”

$OutputFormat.oTsFormat=“yyyy-MM-dd hh:mm:ss”

$OutputFormat.oCodepage=0

$OutputFormat.fileMode=1

}

Write-Progress

-Activity“Executing query, please wait…”-Status” “

$SQLQuery

=“SELECT TimeGenerated,Strings INTO ‘”+$Destination+“‘ FROM ‘C:\garzafx\myeventlog.evtx'”

$rtnVal

=$LogQuery.ExecuteBatch($SQLQuery,$InputFormat,$OutputFormat);

$OutputFormat

=$null;

$InputFormat

=$null;

$LogQuery

=$null;

if

($AutoOpen)

{

try

{

Start-Process($Destination)

}

catch

{

Write-Host$_.Exception.Message -ForegroundColorRed

Write-Host$_.Exception.GetType().FullName -ForegroundColorRed

Write-Host“NOTE: No output file will be created if the query returned zero records!”-ForegroundColorGray

}

}

For more information on:

a. Log Parser Studio 2.0

b. Get-Winevent  (TechNet)

c. select-object  (TechNet)

d. Set-ExecutionPolicy RemoteSigned (TechNet)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s