T-SQL query to Get Details of AlwaysOn HighAvailability Groups AvailabilityReplicas

Here is a T-SQL query to Get Details of AlwaysOn HighAvailability Groups AvailabilityReplicas.

select * into #myTempag_availability_groups from master.sys.availability_groups
select group_id, replica_id, replica_server_name,create_date, modify_date, endpoint_url, read_only_routing_url, primary_role_allow_connections, secondary_role_allow_connections, availability_mode,failover_mode, session_timeout, backup_priority, owner_sid into #myTempar_availability_replicas from master.sys.availability_replicas
select group_id, replica_id, role,operational_state,recovery_health,synchronization_health,connected_state, last_connect_error_number,last_connect_error_description, last_connect_error_timestamp into #myTempar_availability_replica_states from master.sys.dm_hadr_availability_replica_states
select * into #myTempar_ags from master.sys.dm_hadr_availability_group_states
select ar.group_id, ar.replica_id, ar.replica_server_name, ar.availability_mode, (case when UPPER(ags.primary_replica) = UPPER(ar.replica_server_name) then 1 else 0 end) as role, ars.synchronization_health into #myTempar_availabilty_mode from #myTempar_availability_replicas as ar
left join #myTempar_ags as ags on ags.group_id = ar.group_id
left join #myTempar_availability_replica_states as ars on ar.group_id = ars.group_id and ar.replica_id = ars.replica_id
select am1.replica_id, am1.role, (case when (am1.synchronization_health is null) then 3 else am1.synchronization_health end) as sync_state, (case when (am1.availability_mode is NULL) or (am3.availability_mode is NULL) then null when (am1.role = 1) then 1 when (am1.availability_mode = 0 or am3.availability_mode = 0) then 0 else 1 end) as effective_availability_mode
into #myTempar_replica_rollupstate from #myTempar_availabilty_mode as am1 left join (select group_id, role, availability_mode from #myTempar_availabilty_mode as am2 where am2.role = 1) as am3 on am1.group_id = am3.group_id
drop table #myTempar_availabilty_mode
drop table #myTempar_ags
select replica_id,join_state into #myTempar_availability_replica_cluster_states from master.sys.dm_hadr_availability_replica_cluster_states
SELECT
AR.replica_server_name AS [Name],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/AvailabilityGroup[@Name=' + quotename(AG.name,'''') + ']' + '/AvailabilityReplica[@Name=' + quotename(AR.replica_server_name,'''') + ']' AS [Urn],
case 
when ISNULL(AG.automated_backup_preference,3) = 0 then 'PRIMARY'
when ISNULL(AG.automated_backup_preference,3) = 0 then 'PRIMARY'
when ISNULL(AG.automated_backup_preference,3) = 0 then 'PRIMARY'
when ISNULL(AG.automated_backup_preference,3) = 0 then 'PRIMARY'
END as 'Backup_Preference',
case
when ISNULL(arstates.role, 3) = 0 then 'RESOLVING'
when ISNULL(arstates.role, 3) = 1 then 'PRIMARY'
when ISNULL(arstates.role, 3) = 2 then 'SECONDARY'
End AS [Role],
case
when ISNULL(AR.primary_role_allow_connections, 4) = 2 then 'ALL'
when ISNULL(AR.primary_role_allow_connections, 4) = 2 then 'READ_WRITE'
END AS [ConnectionModeInPrimaryRole],
case
when ISNULL(AR.secondary_role_allow_connections, 3) = 0 then 'NO'
when ISNULL(AR.secondary_role_allow_connections, 3) = 1 then 'READ_ONLY'
when ISNULL(AR.secondary_role_allow_connections, 3) = 2 then 'ALL'
END AS [ConnectionModeInSecondaryRole],
case
when ISNULL(arstates.connected_state, 2) = 0 then 'DISCONNECTED'
when ISNULL(arstates.connected_state, 2) = 1 then 'CONNECTED'
END AS [ConnectionState],
(case when arrollupstates.sync_state = 3 then 3 when (arrollupstates.effective_availability_mode = 1 or arrollupstates.role = 1) then arrollupstates.sync_state when arrollupstates.sync_state = 2 then 1 else 0 end) AS [RollupSynchronizationState],
ISNULL(arcs.join_state, 99) AS [JoinState]
FROM
#myTempag_availability_groups AS AG
INNER JOIN #myTempar_availability_replicas AS AR ON (AR.replica_server_name IS NOT NULL) AND (AR.group_id=AG.group_id)
LEFT OUTER JOIN #myTempar_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id
LEFT OUTER JOIN #myTempar_replica_rollupstate AS arrollupstates ON AR.replica_id = arrollupstates.replica_id
LEFT OUTER JOIN #myTempar_availability_replica_cluster_states AS arcs ON AR.replica_id = arcs.replica_id
WHERE
(AG.name='YourAvailabilityGroupName')
ORDER BY
[Name] ASC

DROP TABLE #myTempar_availability_replicas
DROP TABLE #myTempar_availability_replica_states
DROP TABLE #myTempar_replica_rollupstate
DROP TABLE #myTempar_availability_replica_cluster_states
DROP TABLE #myTempag_availability_groups