Availability Group how to determine last Failover time

You can use following PowerShell script to get last failover time

    Get-winEvent -ComputerName ListnerName -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap 

Following lists failover time and direction for each database for all failover events on the server on which this is run, using T-SQL as requested.

-- Script to determine failover times in Availability Group 

;WITH cte_HADR AS (SELECT object_name, CONVERT(XML, event_data) AS data
FROM sys.fn_xe_file_target_read_file('AlwaysOn*.xel', null, null, null)
WHERE object_name = 'error_reported'
)

SELECT data.value('(/event/@timestamp)[1]','datetime') AS [timestamp],
       data.value('(/event/data[@name=''error_number''])[1]','int') AS [error_number],
       data.value('(/event/data[@name=''message''])[1]','varchar(max)') AS [message]
FROM cte_HADR
WHERE data.value('(/event/data[@name=''error_number''])[1]','int') = 1480

You can set up a monitoring script (or a 3rd party SQL monitoring solution), to query the various columns of the sys.dm_hadr_availability_replica_states DMV on a regular schedule (e.g. every 30 seconds) and put a timestamp on it of when you've collected the values.

The particular columns of interest related to failover events are:

• last_connect_error_number
• last_connect_error_description
• last_connect_error_timestamp

Additional columns that are worth tracking:

• role
• operational_state
• connected_state

More detailed description for the above columns:

sys.dm_hadr_availability_replica_states (Transact-SQL)

An overview of all DMVs for AlwaysOn Availability Groups with links to more details is available on MSDN:

Monitor Availability Groups (Transact-SQL)