Notification

Icon
Error

BitLocker encrypted W10 computers with more than one recovery key

Posted: Wednesday, July 8, 2020 6:41:41 PM(UTC)
heybobby1

heybobby1

Member Original PosterPosts: 41
1
Like
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?

Code:
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

Active Discussions

Lansweeper Software List by Computer/Server Name
by  Andy.S  
Go to last post Go to first unread
Last post: Yesterday at 8:06:43 AM(UTC)
Lansweeper Asset: Duplicate Serial Number (Built-in)
by  brodiemac-too   Go to last post Go to first unread
Last post: 8/5/2020 1:28:56 PM(UTC)
Lansweeper A Simple Report (or so I thought)
by  Andy.S  
Go to last post Go to first unread
Last post: 8/5/2020 11:30:45 AM(UTC)
Lansweeper Paid Report Writer Service
by  endyk   Go to last post Go to first unread
Last post: 8/4/2020 11:56:20 AM(UTC)
Lansweeper Multiple Computers per User
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/30/2020 12:43:38 PM(UTC)
Lansweeper Workstations with No AV
by  brodiemac-too   Go to last post Go to first unread
Last post: 7/29/2020 7:44:53 PM(UTC)
Lansweeper AV Audit Report
by  RC62N  
Go to last post Go to first unread
Last post: 7/29/2020 5:17:42 PM(UTC)