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
