Now that the extended events session is setup we can use some queries to query information about our AGs and error messages happening on our servers. First to query when the server failover and becomes your primary server query the event availability_replica_state_change. The first 10 lines just read in the files for the extended event session so you don’t have to identify the exact filenames. Then we parse the xml event for get the timestamp, previous state, current state, repliace name, and group name for the event FROM the filenames we collected before. In the WHERE clause were are looking for when the state has changed to PRIMARY_NORMAL indicating a failover to the server.
Read on for scripts showing what she extends and also how to query this data.