Just a quick blog post to share a query, which I wrote to monitor availability groups and replicas and the associated databases:

WITH [AvailabilityGroupReplicaCTE]
AS (
SELECT dc.[database_name]
,dr.[synchronization_state_desc]
,dr.[suspend_reason_desc]
,dr.[synchronization_health_desc]
,dr.[replica_id]
,ar.[availability_mode_desc]
,ar.[primary_role_allow_connections_desc]
,ar.[secondary_role_allow_connections_desc]
,ar.[failover_mode_desc]
,ar.[endpoint_url]
,ar.[owner_sid]
,ar.[create_date]
,ar.[modify_date]
,dr.[recovery_lsn]
,dr.[truncation_lsn]
,dr.[last_sent_lsn]
,dr.[last_sent_time]
,dr.[last_received_lsn]
,dr.[last_received_time]
,dr.[last_hardened_lsn]
,dr.[last_hardened_time]
,dr.[last_redone_lsn]
,dr.[last_redone_time]
,dr.[redo_queue_size]
,dr.[log_send_queue_size]
FROM [sys].[dm_hadr_database_replica_states] dr
INNER JOIN [sys].[availability_databases_cluster] dc
ON dr.[group_database_id] = dc.[group_database_id]
INNER JOIN [sys].[availability_replicas] ar
ON ar.[replica_id] = dr.[replica_id]
WHERE dr.[is_local] = 1
)
,[AvailabilityGroupReplicaDatabaseState] (
[ReplicaID]
,[ReplicaDBName]
,[ReplicaServerName]
,[JoinState]
,[Role]
,[AvailabilityMode]
,[SynchronizationState]
,[SynchronizationHealth]
,[OperationalState]
,[ConnectedState]
,[SuspendReason]
,[RecoveryHealth]
,[RecoveryLSN]
,[TruncationLSN]
,[LastSentLSN]
,[LastSentTime]
,[LastReceivedLSN]
,[LastReceivedTime]
,[LastHardenedLSN]
,[LastHardenedTime]
,[LastRedoneLSN]
,[LastRedoneTime]
,[RedoQueueSize]
,[LogSendQueueSize]
,[PrimaryRoleAllowConnections]
,[SecondaryRoleAllowConnections]
,[FailoverMode]
,[EndPointURL]
,[Owner]
,[CreateDate]
,[ModifyDate]
)
AS (
SELECT c.[replica_id]
,c.[database_name]
,cs.[replica_server_name]
,cs.[join_state_desc]
,rs.[role_desc]
,c.[availability_mode_desc]
,c.[synchronization_state_desc]
,c.[synchronization_health_desc]
,rs.[operational_state_desc]
,rs.[connected_state_desc]
,c.[suspend_reason_desc]
,rs.[recovery_health_desc]
,c.[recovery_lsn]
,c.[truncation_lsn]
,c.[last_sent_lsn]
,c.[last_sent_time]
,c.[last_received_lsn]
,c.[last_received_time]
,c.[last_hardened_lsn]
,c.[last_hardened_time]
,c.[last_redone_lsn]
,c.[last_redone_time]
,c.[redo_queue_size]
,c.[log_send_queue_size]
,c.[primary_role_allow_connections_desc]
,c.[secondary_role_allow_connections_desc]
,c.[failover_mode_desc]
,c.[endpoint_url]
,sl.[name]
,c.[create_date]
,c.[modify_date]
FROM [AvailabilityGroupReplicaCTE] c
INNER JOIN [sys].[dm_hadr_availability_replica_states] rs
ON rs.[replica_id] = c.[replica_id]
INNER JOIN [sys].[dm_hadr_availability_replica_cluster_states] cs
ON cs.[replica_id] = c.[replica_id]
INNER JOIN [sys].[syslogins] sl
ON c.[owner_sid] = sl.[sid]
)
SELECT *
FROM [AvailabilityGroupReplicaDatabaseState];