Execution Flow
START
foreach $computer in $ComputerName [pipeline]
Source 1: Application Log (always)
Get-WinEvent LogName=Application Id=1480,19407 Since=$Since
Collect lease-expired times (EventID 19407) for Forced detection
foreach EventID 1480: _ParseAGName / _ParseRole / _GetFailoverType
FailoverType: Forced(19407≤5min) / Planned(user/manual) / Automatic / Unknown
Apply $AvailabilityGroup filter if set
Source 2: WSFC Operational Log (if -IncludeClusterLog)
Get-WinEvent Microsoft-Windows-FailoverClustering/Operational Id=1641
Add events as Source='ClusterLog', NewRole='PRIMARY'
Source 3: SQL role_start_time (if -SqlInstance)
Invoke-DbaQuery: dm_hadr_availability_replica_states
current_configuration_commit_start_time_utc (approximation)
Sort results by FailoverTime DESC
ShouldProcess → write TXT + CSV to $OutputPath
Return $allResults[]
DONE
Synopsis
Parses the Windows Application Event Log for AG role-transition events (EventID 1480) since a configurable lookback date. Detects forced failovers via EventID 19407 (lease expiration) within 5 minutes of a role-change event. Optionally supplements with WSFC Operational Log (EventID 1641) and SQL DMV dm_hadr_availability_replica_states role timestamps.
Requires read access to the Windows Event Log of the target computer. SQL connection is only needed when -SqlInstance is specified. No dbatools needed for event log queries alone.
Syntax
Get-sqmAlwaysOnFailoverHistory
[-ComputerName <String[]>]
[-SqlInstance <String>]
[-SqlCredential <PSCredential>]
[-AvailabilityGroup <String>]
[-Since <DateTime>]
[-IncludeClusterLog ]
[-OutputPath <String>]
[-ContinueOnError ]
[-EnableException ]
Parameters
Parameter Type Default Description -ComputerName String[] $env:COMPUTERNAME Target computer(s). Remote Event Log queries via Get-WinEvent -ComputerName. Pipeline-capable. -SqlInstance String , SQL Server instance for supplementary role_start_time data (optional). -SqlCredential PSCredential , Credential for the SQL connection. -AvailabilityGroup String all AGs Filter results to a specific AG name. -Since DateTime 30 days ago Earliest event timestamp to include. -IncludeClusterLog Switch false Also query the WSFC Operational Log (EventID 1641). Only available on WSFC nodes. -OutputPath String C:\System\WinSrvLog\MSSQL Directory for TXT and CSV report files. -ContinueOnError Switch false Continue with the next computer on error. -EnableException Switch false Throw terminating exceptions. -NoOpen switch $false Do not automatically open the generated report after creation.
Return Value
Property Description ComputerName Source computer for this event. AvailabilityGroup AG name parsed from the event message. FailoverTime Event timestamp (local time). NewRole PRIMARY / SECONDARY / RESOLVING / UNKNOWN. FailoverType Planned / Automatic / Forced / Unknown. EventId Windows Event ID (1480 / 19407 / 1641 / null). Source ApplicationLog / ClusterLog / RoleStartTime. Message First 200 characters of the event message.
FailoverType Detection Logic
Type Detection Rule Forced EventID 19407 (lease expiration) occurred ≤ 5 minutes before the EventID 1480 role-change. Planned EventID 1480 message contains "user", "manual", or "manuell". Automatic EventID 1480 message contains "automatic", "WSFC", or "automatisch". Unknown None of the above patterns matched.
Examples
Example 1, Check last 30 days on local computer
Get-sqmAlwaysOnFailoverHistory copy
Example 2, Check last 90 days on a specific server
Get-sqmAlwaysOnFailoverHistory -ComputerName "SQL01" -Since (Get-Date ).AddDays(-90)copy
Example 3, Full check with cluster log and SQL DMV
Get-sqmAlwaysOnFailoverHistory -ComputerName "SQL01" `
-SqlInstance "SQL01" -AvailabilityGroup "AG_Prod" -IncludeClusterLog copy
Example 4, Multiple servers via pipeline
"SQL01" ,"SQL02" | Get-sqmAlwaysOnFailoverHistory -Since (Get-Date ).AddDays(-7)copy