Lately I’ve been toying with the idea of using PowerShell to parse the Windows event logs and possibly adding that to my Inventory scripts. I like the idea of pulling out the important errors and messages into a central place for easy viewing/reporting. Depending on what platform you’re running on, there are a couple of options available.
Get-EventLog
The first cmdlet for reading Windows event logs is the aptly named Get-EventLog. Want to print the contents of the Application Log?
Get-EventLog -logname application
If you actually run that command, you’ll get a ton of output that probably isn’t all that useful. It would be handier if we could apply a filter or two, and we can. Maybe we only want SQL Server messages:
Get-EventLog -LogName application -Source MSSQLSERVER
You can even filter by date, so if you’re parsing the log on a daily basis, you don’t get a bunch of duplicate entries.
Get-EventLog -LogName application -After 9/20/2012 -Source MSSQLSERVER
Or combine Get-EventLog with Where-Object to filter on the message text itself, perhaps to just return events with the word “error”.
Get-EventLog -LogName APPLICATION -After 09/01/2012 | Where-Object { $_.Message -like '*error*' }
The downside of using Get-EventLog with Where-Object is that it reads through the entire file before filtering. This can take quite a long time, especially if you’re reading the event log on a remote server. Fortunately, if you’re running on Windows Server 2008, there’s a better way.
Get-WinEvent
The advantage of this cmdlet is the ability to filter before piping to Where-Object. This makes it more efficient to filter remote event logs, rather than dragging the entire log back and filtering through it locally.
Get-WinEvent -LogName application
To filter the output of Get-WinEvent, we use the -FilterHashTable parameter, which makes it a little less intuitive than Get-EventLog, if you ask me. For example, to parse out SQL Server messages, we would run something like this:
Get-WinEvent -FilterHashtable @{logname='application'; ProviderName='MSSQLSERVER'}
You might have noticed that, in the first example, I used the -LogName parameter to specify the application log, but I didn’t do that in the second example. I did this because you can’t use -LogName and -FilterHashTable together. Therefore, when I used -FilterHashTable, I simply made the log name one of the filters.
Want to add a date parameter? We can do that too.
Get-WinEvent -FilterHashtable @{logname='application'; ProviderName='MSSQLSERVER'; StartTime=(Get-Date).date}
And we can still combine it with the Where-Object cmdlet to filter even further.
Get-WinEvent -FilterHashtable @{logname='application'; ProviderName='MSSQLSERVER'} | where-object { $_.Message -like '*error*' }
Bummer
Unfortunately, in my work environment, we have a hefty number of Windows 2003 servers, and using Get-EventLog is just too slow, especially for servers in Asia. Therefore I won’t be able to add this functionality to my Inventory. At least, not at this time. But if your environment is workable, this info would be a great addition.
I gotta check out Get-WinEvent. Can’t believe I didn’t know about this.
After reading your PowerShell posts and watching Allen White’s PowerShell 101 presentation at 24Hr SQL Pass…I’m convinced that I’ve got to tackle PowerShell next!
Have you done anything with the new PowerShell v3 or PowerShell ISE out of curiosity?
While it’s not perfect Get-EventLog -Newest is a lot faster than -After on slow links since it doesn’t seem to parse the entire log. The number of records returned can be tailored to your connection speed/log size.
Justin – It’s pretty handy. And fast!
Jason – Nope, I haven’t played with v3 or the ISE yet.
AJ – Thanks for the tip.
With the Get-WinEvent command you could be using the -FilterXml Parameter and provide a XML File containing all the search criterias.
The XML can be easily created using the “Create Custom View” in the Event Viewer and Copy&Paste the XML Code into a new XML file.
Something like this:
*[System[Provider[@Name=’MSSQLSERVER’] and (Level=1 or Level=2 or Level=3)]]
The Levels stand for Critical, Error and Warning. You can of course add more criterias like Task category and Keywords, Users, Computers, Dates and etc. you wish to filter for.
PS Command would look something like this:
Get-WinEvent -FilterXml ((Get-Content C:\MyXMLs\MyCustomXML.xml))
You could then enhance the script to import directly into SQL without exporting a csv or something else.
I believe this could be even quicker than using a FilterHashtable
You can use the gmwi command such as:
gwmi -computername sig-aim-clus -Query “Select * from Win32_NTlogEvent Where LogFile = ‘System’ AND (Type =’Error’ OR Type = ‘Warning’) AND TimeWritten >= ‘20140501000000.000000-000′”
I have timed it, it is much faster as it does not retrieve the entire log file. I converted this from a vbscript function I created previously. The only caveat is that you have to modify the TimeWritten portion of the query. This will enable you to access Windows Server 2003 R2 and earlier editions.
Robert, that is EPIC WIN. Or as a coworker just told me, “baller as f***”. I’d forgotten you could do that. And it just saved us a few hours of work! Thanks!
This is faster:
Get-WmiObject Win32_NTLogEvent -Filter “Logfile=’system’ AND SourceName=’Service Control Manager’ AND Message LIKE ‘%Service%'” | select Message