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.
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 from the following window.
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.
10. After dropping the TOP 10 reference, I had pressed the following button to export the T-SQL commands to get a PowerShell script.
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 #
#Log Type: EVTLOG
#Generated: 7/25/2013 7:14:54 AM
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
-Activity“Executing query, please wait…”-Status” “
=“SELECT TimeGenerated,Strings INTO ‘”+$Destination+“‘ FROM ‘C:\garzafx\myeventlog.evtx'”
Write-Host“NOTE: No output file will be created if the query returned zero records!”-ForegroundColorGray
For more information on:
b. Get-Winevent (TechNet)
c. select-object (TechNet)
d. Set-ExecutionPolicy RemoteSigned (TechNet)