BitLocker encrypted W10 computers with more than one recovery key

[heybobby1]

Hi all,

I have a report for reporting on the SystemDrive BitLocker encryption status. I would like to add if a Recovery Key exists in AD for the computer. I've done this but it now reports a computer more than once if there is more than one recovery key saved in AD. I only want it to appear once but report BitLockerRecoveryKeyADBackup = Yes/No

I'm a bit rusty at creating LS reports and it's making my head hurt looking at it now. Can anyone help me figure this out?

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname As OS,
  Case
    When tblAssetCustom.Custom8 Like '%Laptop%' Then 'Laptop'
    When tblAssetCustom.Custom8 Like '%PC%' Then 'Desktop'
    When tblAssetCustom.Custom8 Like '%CAD Station%' Then 'Desktop'
    Else 'Unknown'
  End As ChassisType,
  tblAssetCustom.Model,
  tblEncryptableVolume.DriveLetter,
  Case
    When tblEncryptableVolume.ProtectionStatus = 0 Then 'Off'
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'On'
    Else 'Unknown'
  End As ProtectionStatus,
  tblEncryptableVolume.LastChanged As EncryptableVolumeLastChanged,
  Case
    When tblBitLockerRecoveryKey.RecoveryKey Is Not Null Then 'Yes'
    Else 'No'
  End As BitLockerRecoveryKeyADBackup,
  tblBitLockerRecoveryKey.LastChanged As BitLockerRecoveryKeyLastScanned,
  tblAssetCustom.Custom1 As Office,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Custom2 As Country,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate As WarrantyDate,
  tblAssets.Firstseen As FirstSeen,
  tblAssets.Lastseen As LastSeen,
  tblAssetCustom.Custom3 As [User],
  tblAssets.Username As LastUser,
  tblState.Statename As State
From tblAssets
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblBitLockerRecoveryKey On tblBitLockerRecoveryKey.AdObjectId =
    tblADComputers.ADObjectID
  Left Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Left Join tblEncryptableVolume On tblEncryptableVolume.AssetId =
    tblAssets.AssetID
Where tblEncryptableVolume.DriveLetter Like tblOperatingsystem.SystemDrive And
  tblAssets.Lastseen > GetDate() - 30 And tblComputersystem.Domainrole < 2
Order By Country,
  Office,
  tblAssets.AssetName