select  db.name,
        db.database_id,
        ag.name as GroupName,
        state_desc,
        recovery_model_desc,
        AGDB.truncation_lsn,
        Rep.replica_server_name,
        DBRepStats.is_primary_replica,
        DBRepStats.synchronization_health_desc,
        DBRepStats.database_state_desc,
        DBRepStats.end_of_log_lsn,
        DBRepStats.last_sent_lsn,
        DBRepStats.last_sent_time,
        DBRepStats.last_received_lsn,
        DBRepStats.last_received_time,
        DBRepStats.last_hardened_lsn,
        DBRepStats.last_hardened_time
from sys.databases as db
    left outer join sys.availability_databases_cluster as AGDB on db.group_database_id = AGDB.group_database_id
    left outer join sys.availability_replicas as Rep on db.replica_id = Rep.replica_id
    left outer join sys.dm_hadr_database_replica_states as DBRepStats on db.group_database_id = DBRepStats.group_database_id
    left outer join sys.availability_groups as AG on DBRepStats.group_id = AG.group_id
where db.database_id > 4
go