Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cleveland DBA

Colleen Morrow is a Database Engineer for a software company in Westlake, Ohio. She has worked in the IT industry for 15+ years, doing everything from technical support to development to database administration on Informix, Oracle, and SQL Server platforms.

Parsing Windows event logs with PowerShell

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.

Comments

Leave a comment on the original post [colleenmorrow.com, opens in a new window]

Loading comments...