;WITH LastBackups AS (
    SELECT 
        bs.database_name,
        bs.type,
        bs.backup_finish_date,
        bs.backup_start_date,
        ROW_NUMBER() OVER (
            PARTITION BY bs.database_name, bs.type
            ORDER BY bs.backup_finish_date DESC
        ) AS rn
    FROM msdb.dbo.backupset AS bs
)
SELECT 
    lb.database_name AS dbname,
    lb.type,
    DATEDIFF(SECOND, lb.backup_finish_date, GETDATE()) AS time_since_last_backup,
    DATEDIFF(SECOND, lb.backup_start_date, lb.backup_finish_date) AS duration,
    db.recovery_model AS db_recovery_model
FROM LastBackups lb
JOIN sys.databases db ON lb.database_name = db.name
LEFT JOIN (
    SELECT AGDatabases.database_name
    FROM sys.dm_hadr_availability_group_states States
    INNER JOIN master.sys.availability_groups Groups
        ON States.group_id = Groups.group_id
    INNER JOIN sys.availability_databases_cluster AGDatabases
        ON Groups.group_id = AGDatabases.group_id
    WHERE States.primary_replica != @@SERVERNAME OR States.primary_replica IS NULL
) ag ON lb.database_name = ag.database_name
WHERE lb.rn = 1
  AND ag.database_name IS NULL
ORDER BY lb.database_name;