SQL with Manoj

SELECT 
	 ag.NAME AS 'AG Name'
	,ar.replica_server_name AS 'AG Replica Server'
	,DB_NAME(drs.database_id) AS 'Database Name'
	,CASE 
		WHEN ars.is_local = 1
			THEN N'LOCAL'
		ELSE 'REMOTE'
	END AS 'Is AG Replica Local'
	,CASE 
		WHEN ars.role_desc IS NULL
			THEN N'DISCONNECTED'
		ELSE ars.role_desc
	END AS 'AG Replica Role'
	,ar.availability_mode_desc AS 'Sync Mode'
	,drs.synchronization_state_desc AS SyncState
	,agl.dns_name AS 'Listener Name'
	,drs.last_hardened_lsn
	,drs.last_hardened_time
	,datediff(s, last_hardened_time, getdate()) / 60 AS 'Seconds Behind Primary'
	,drs.last_commit_time

FROM sys.availability_groups AS ag 

INNER JOIN sys.availability_replicas AS ar 
ON ag.group_id = ar.group_id

INNER JOIN sys.dm_hadr_availability_replica_states AS ars 
ON ar.replica_id = ars.replica_id

INNER JOIN sys.dm_hadr_database_replica_states drs 
ON ag.group_id = drs.group_id
AND drs.replica_id = ars.replica_id

INNER JOIN sys.availability_group_listeners AS agl
ON agl.group_id = ars.group_id

ORDER BY datediff(s, last_hardened_time, getdate()) DESC;