SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.


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.


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*' } 


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.

Cleveland DBA

Colleen Morrow is a database professional living in Cleveland, OH who has been working with database systems since 1996. For more than 12 years, she was a Database Administrator at a large law firm where she developed an appreciation for auditing, automation, and performance tuning. Since that time she has worked with clients in the healthcare, manufacturing, software, and distribution/freight delivery industries. Colleen is currently a Senior Consultant at Fortified Data.


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

Loading comments...