Finding SQL Server Cluster Failover Events using PowerShell v3

Here’s a 2 node cluster running Windows Server 2008R2 for the OS and a single instance of SQL Server.  If the name of the clustered service/application is known, querying the event log using PowerShell’s remoting features implicitly with Invoke-Command (aliased to icm) makes finding cluster failover events pretty quick.  In this case matching against string  MSSQLSERVER is used, as the full name of the clustered service/app is 'SQL Server (MSSQLSERVER)'.

What’s the cluster look like now?

7/22/2013 2:01:35 PM :: user@deadair :: D:\Dropbox\bin
[6307] #   icm SQL00 {Get-ClusterGroup} | ft Name, OwnerNode, State, PSComputerName –a   

Name                     OwnerNode  State  PSComputerName
----                     ---------  -----  --------------
PORTAL_DTC               sql01      Online SQL00
SQL Server (MSSQLSERVER) sql01      Online SQL00
Cluster Group            sql01      Online SQL00
Available Storage        sql01      Online SQL00

Event ID 1201 is logged when resource groups are brought online within the cluster, so we’re limiting the results to that Event ID.  Running the Get-WinEvent cmdlet against all cluster nodes (just two nodes in this case – SQL00 and SQL01) and assigning the results to a variable allows sorting of entries from all nodes.  Otherwise results would be sorted within the context of the the current remote target(PSComputerName) node.

7/22/2013 2:14:19 PM :: user@deadair :: D:\Dropbox\bin
[6316] #   $FailoverEvents = icm SQL00, SQL01 {Get-WinEvent -FilterHashtable @{LogName='Microsoft-Windows-FailoverClustering/Operational';ID = 1201} | where Message -match MSSQLSERVER }  

Now to sort the event log entries…

7/22/2013 2:15:04 PM :: user@deadair :: D:\Dropbox\bin
[6317] #   $FailoverEvents | sort TimeCreated -desc | ft –a   

   ProviderName: Microsoft-Windows-FailoverClustering

TimeCreated              Id LevelDisplayName Message                                                                                                          PSComputerName
-----------              -- ---------------- -------                                                                                                          --------------
7/19/2013 10:26:24 PM  1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL01
7/13/2013 10:33:05 PM  1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL00
7/13/2013 10:09:08 PM  1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL01
7/6/2013 10:57:32 PM   1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL00
7/6/2013 10:45:58 PM   1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL01
6/1/2013 10:40:18 PM   1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL00
6/1/2013 10:18:50 PM   1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL01

Summary: Combining Get-WinEvent with Remoting allows for a very quick recon of events in a cluster.

Prefer a one-liner instead of using a variable? Just perform the sort outside the Get-WinEvent statement block.  Let’s add the day of the week, too! 

  <# Cluster Failover Events #> icm SQL00, SQL01 `
{ Get-WinEvent -FilterHashtable @{LogName='Microsoft-Windows-FailoverClustering/Operational';ID = 1201} `
| where Message -match MSSQLSERVER } `
| sort TimeCreated -desc `
| ft @{N='DayofWeek';E={($_.TimeCreated).DayofWeek}} , TimeCreated, ID, Message -a