I am using below backup script taken from http://www.ludovicocaldara.net/dba/sql-server-last-database-backup/ link to audit my servers for backups. This works great except when I have spilt backups.

For splitbackups it gives lots of rows depending on no. of split backup files. For example, if I have 4 split FULL backup files and 4 split diff backup files, it gives 16 rows of output. This is because of the LEFT JOIN used. I need help in fixing the script to remove all duplicates and provide correct output. For example, it should only give 4 split FULL backup files and 4 diff backup files in output.

WITH db
     AS (SELECT [Instance] = @@SERVERNAME,
                [Database] = name,
                [RecoveryMode] = DATABASEPROPERTYEX(name, 'Recovery'),
                [CreationTime] = crdate,
                [Status] = DATABASEPROPERTYEX(name, 'Status')
         FROM master..sysdatabases
         WHERE name != 'tempdb'),
lastfull
     AS (SELECT *
         FROM
         (
             SELECT [Database] = s.database_name,
                    --[Type]   = s.type,
                    [LastFullDate] = CONVERT( VARCHAR, s.backup_finish_date, 120),
                    --[LastFullSize]   = s.backup_size,
                    [LastFullDevice] = f.physical_device_name,
                    --[LastFullDevTyp] = f.device_type,
                    [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC)
             FROM msdb.dbo.backupset s,
                  msdb.dbo.backupmediafamily f
             WHERE s.media_set_id = f.media_set_id
                   AND s.type = 'D'
         -- and f.device_type = 7 -- only backup devices
         ) f
         WHERE nrank = 1),
lastdiff
     AS (SELECT *
         FROM
         (
             SELECT [Database] = s.database_name,
                    --[Type]   = s.type,
                    [LastDiffDate] = CONVERT( VARCHAR, s.backup_finish_date, 120),
                    --[LastDiffSize]   = s.backup_size,
                    [LastDiffDevice] = f.physical_device_name,
                    --[LastDiffDevTyp] = f.device_type,
                    [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC)
             FROM msdb.dbo.backupset s,
                  msdb.dbo.backupmediafamily f
             WHERE s.media_set_id = f.media_set_id
                   AND s.type = 'I'
         -- and f.device_type = 7 -- only backup devices
         ) d
         WHERE nrank = 1),
lastlog
     AS (SELECT *
         FROM
         (
             SELECT [Database] = s.database_name,
                    --[Type]   = s.type,
                    [LastLogDate] = CONVERT( VARCHAR, s.backup_finish_date, 120),
                    --[LastLogSize]   = s.backup_size,
                    [LastLogDevice] = f.physical_device_name,
                    --[LastLogDevTyp] = f.device_type,
                    [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC)
             FROM msdb.dbo.backupset s,
                  msdb.dbo.backupmediafamily f
             WHERE s.media_set_id = f.media_set_id
                   AND s.type = 'L'
         -- and f.device_type = 7 -- only backup devices
         ) l
         WHERE nrank = 1)
SELECT db.[Instance] AS ServerName,
    db.[Database] AS DatabaseName,
    db.[RecoveryMode] AS ReconveyModel,
    db.[CreationTime] AS DBCreationTime,
    db.[Status] AS DatabaseStatus,
    ISNULL(CONVERT( VARCHAR, lastfull.LastFullDate), 'Never') AS LastFullBackupDate,
    ISNULL(lastfull.[LastFullDevice], 'None') AS LastFullBackupPath,
    ISNULL(CONVERT( VARCHAR, lastdiff.LastDiffDate), 'Never') AS LastDiffBackupDate,
    ISNULL(lastdiff.[LastDiffDevice], 'None') AS LastDiffBackupPath,
    ISNULL(CONVERT( VARCHAR, lastlog.[LastLogDate]), 'Never') AS LastLogBackupDate,
    ISNULL(lastlog.[LastLogDevice], 'None') AS LastLogBackupPath
FROM db
    LEFT OUTER JOIN lastfull ON(db.[Database] = lastfull.[Database])
    LEFT OUTER JOIN lastdiff ON(db.[Database] = lastdiff.[Database])
    LEFT OUTER JOIN lastlog ON(db.[Database] = lastlog.[Database]);  

Edit:

Let's say I am taking my Full and Diff backups as below:

backup database AdventureWorks to disk =
'D:\MSSQL\AdventureWorks_1_041320161807.bak', disk =
'D:\MSSQL\AdventureWorks_2_041320161807.bak', disk =
'D:\MSSQL\AdventureWorks_3_041320161807.bak', disk =
'D:\MSSQL\AdventureWorks_4_041320161807.bak' with stats = 10;

backup database AdventureWorks
to disk = 'D:\MSSQL\AdventureWorks_1_041320161808.bak',
disk = 'D:\MSSQL\AdventureWorks_2_041320161808.bak',
disk = 'D:\MSSQL\AdventureWorks_3_041320161808.bak',
disk = 'D:\MSSQL\AdventureWorks_4_041320161808.bak'
with stats = 10, differential;    

With the above script I am getting result as below. Notice that it has 16 rows. 4*4.

What I need is, the script should show output as 4 Full backup files and 4 diff backup files.i.e. only the actual number of file. Currently due to JOIN it is giving 16 rows. Please let me know if you need any further clarification.