MSSQL

SELECT

  ag.name as group_name,

  ISNULL(ags.primary_recovery_health, 2) as primary_recovery_health,

  ISNULL(ags.primary_replica, 'Unknown') as primary_replica,

  ISNULL(

    ags.secondary_recovery_health, 2

  ) as secondary_recovery_health,

  ags.synchronization_health as synchronization_health

FROM

  sys.dm_hadr_availability_group_states ags

JOIN sys.availability_groups ag ON ag.group_id = ags.group_id