CMS query all availability groups status

CMS query all availability groups status

select

hadrc.cluster_name,

SERVERPROPERTY('MachineName') AS [ServerName],

SERVERPROPERTY('InstanceName') AS [Instance],

SERVERPROPERTY('Edition') AS [Edition],

SERVERPROPERTY('ProductVersion') AS [ProductVersion],

Left(@@Version, Charindex('-', @@version) - 2) As VersionName,

lip.state_desc,

ip_address,

AGC.name as AG_Name,

isnull(l.dns_name, @@SERVERNAME )+':'+(CAST(port as varchar(8))) as Listener,

case WHEN is_conformant is null then 0 ELSE is_conformant END as is_conformant,

CASE

WHEN role=1 THEN replica_server_name

ELSE (select replica_server_name from sys.dm_hadr_availability_replica_cluster_states RCS

INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id where role=1)

END as active_node,

CASE

WHEN role=2 THEN replica_server_name

ELSE (select top 1 replica_server_name from sys.dm_hadr_availability_replica_cluster_states RCS

INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id where role=2)

END as passive_node,

hadrc.quorum_state_desc,

hadrc.quorum_type_desc,

CAST(CASE AGC.failure_condition_level

WHEN 1 THEN 'SQL Server service is down/SQL Server AlwaysOn Lease

Timeout'

WHEN 2 THEN 'Instance of SQL Server does not connect to cluster/availability replica is in failed state'

WHEN 3 THEN 'Critical SQL Server internal errors / Default'

WHEN 4 THEN 'Moderate SQL Server internal errors'

WHEN 5 THEN 'Any qualified failure conditions/Exhaustion of SQL Engine worker-threads/Detection of an unsolvable'

END as varchar(110)) as AutomaticFailoverCondition,

RCS.join_state_desc,

ARS.role_desc,

ARS.connected_state_desc,

ARS.operational_state_desc,

ARS.synchronization_health_desc,

CAST(Getdate() as date) as [Timestamp],

CHECKSUM(CAST(SERVERPROPERTY('MachineName') as varchar(128))+ AGC.name +CONVERT(varchar(10),getdate(),102))as LookupKey

FROM sys.availability_groups_cluster AS AGC

INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id

INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id

LEFT OUTER JOIN sys.availability_group_listeners l ON l.group_id = ARS.group_id

LEFT OUTER join sys.availability_group_listener_ip_addresses lip on lip.listener_id=l.listener_id

outer apply (select * from sys.dm_hadr_cluster) as hadrc

WHERE replica_server_name = SERVERPROPERTY('ServerName')