This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject

→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Report to get computers with Bitlocker enable and Recovery Keys empty

Asoumpholphakdy
Engaged Sweeper II

‎01-17-2022 04:27 PM

Hi everybody,

First, thanks for all the help I found before in the forum for what I needed.
Now I'm lil bit stuck and ask for your help.

I try to do my own report but not really working 😕 I don't know SQL but still trying to learn when I got time.

I need a report that shows my computers with Windos 10 where the Bitlocker Status is enable and also if recovery keys are not found.
I already got a script by Powershell to save the recovery key in AD that I want to deploy for this computers.

We already have a GPO for that but some computers in the Active Directory don't have the recovery key.
I want to make sure that we saved all the recovery keys in AD by forcing to back up the recovery key.

Hope guys can help me.



Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End Like 'ON' And tsysOS.OSname Like 'Win 10'
Order By tblAssets.AssetName
Labels:
0 Kudos
4 REPLIES 4
Asoumpholphakdy
Engaged Sweeper II

‎01-19-2022 10:28 AM

Hello Looktall,

Thanks for your help
That's the perfect thing I was looking for
Hope it can help other people who needed it too.
0 Kudos
looktall
Engaged Sweeper III

‎01-19-2022 09:50 AM

Tony S. wrote:
Hi everybody,

First, thanks for all the help I found before in the forum for what I needed.
Now I'm lil bit stuck and ask for your help.

I try to do my own report but not really working 😕 I don't know SQL but still trying to learn when I got time.

I need a report that shows my computers with Windos 10 where the Bitlocker Status is enable and also if recovery keys are not found.
I already got a script by Powershell to save the recovery key in AD that I want to deploy for this computers.

We already have a GPO for that but some computers in the Active Directory don't have the recovery key.
I want to make sure that we saved all the recovery keys in AD by forcing to back up the recovery key.

Hope guys can help me.



Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End Like 'ON' And tsysOS.OSname Like 'Win 10'
Order By tblAssets.AssetName


Here's one i created by pulling bits and pieces from here and there.
It shows if the TPM is enabled in bios, if bitlocker is enabled, if the key is in AD (it can take 24 hours for that to update), if the GPO is applied and if secure boot is enabled.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  Case
    When SubQuery2.IsEnabled_InitialValue = 1 Then 'Yes'
    Else 'No'
  End As [TPM Enabled in BIOS],
  Case
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'Yes'
    Else 'No'
  End As [Bitlocker Enabled],
  Case
    When
      tblADComputers.ADObjectID Not In (Select
          tblBitLockerRecoveryKey.AdObjectId
        From tblBitLockerRecoveryKey) Then 'No'
    Else 'Yes'
  End As [Key In AD],
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End As [GPO Applied],
  Case
    When SubQuery3.Valuename Is Not Null And SubQuery3.Value <> '' Then 'Yes'
    Else 'No'
  End As [SecureBoot Enabled],
  Case
    When tblPortableBattery.AssetID Is Null Then 'Desktop'
    Else 'Laptop'
  End As [Desktop/Laptop],
  tblAssets.Domain,
  tblAssets.OScode + '.' + tblAssets.BuildNumber As Build,
  tblAssets.Version As [OS Version],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysIPLocations.IPLocation As IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  TsysLastscan.Lasttime As LastRegistryScan,
  SubQuery1.Regkey,
  SubQuery1.Valuename,
  SubQuery1.Value,
  SubQuery1.Lastchanged,
  Case
    When TsysLastscan.Lasttime < GetDate() - 1 Then
      'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.'
  End As Comment,
  SubQuery2.SpecVersion,
  SubQuery2.IsEnabled_InitialValue,
  tblAssetCustom.State,
  SubQuery3.Regkey As Regkey1,
  SubQuery3.Valuename As Valuename1,
  SubQuery3.Value As Value1
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where tblRegistry.Regkey Like '%SOFTWARE\Policies\Microsoft\FVE' And
      tblRegistry.Valuename = 'OSActiveDirectoryBackup') SubQuery1 On
      SubQuery1.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Lastchanged,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Control\SecureBoot\State' And
      tblRegistry.Valuename = 'UEFISecureBootEnabled' And tblRegistry.Value =
      '1') SubQuery3 On SubQuery3.AssetID = tblAssets.AssetID
  Left Join lansweeperdb.dbo.tblPortableBattery On tblAssets.AssetID =
      tblPortableBattery.AssetID
  Inner Join lansweeperdb.dbo.tblADComputers On tblAssets.AssetID =
      tblADComputers.AssetID
  Inner Join lansweeperdb.dbo.tsysAssetTypes On tsysAssetTypes.AssetType =
      tblAssets.Assettype
  Inner Join lansweeperdb.dbo.tblEncryptableVolume On tblAssets.AssetID =
      tblEncryptableVolume.AssetId
  Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Join (Select Top 1000000 tblTPM.AssetId,
      tblTPM.SpecVersion,
      tblTPM.IsEnabled_InitialValue
    From tblTPM) SubQuery2 On SubQuery2.AssetId = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblEncryptableVolume.DriveLetter = 'C:' And
  TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
  tblAssets.AssetName
Powen
Engaged Sweeper II
In response to looktall

‎02-23-2022 06:59 AM

HI looktall,

Excuse me, if I only need to find out the computer with bitlocker enabled, how can I modify the report? thanks




looktall wrote:
Tony S. wrote:
Hi everybody,

First, thanks for all the help I found before in the forum for what I needed.
Now I'm lil bit stuck and ask for your help.

I try to do my own report but not really working 😕 I don't know SQL but still trying to learn when I got time.

I need a report that shows my computers with Windos 10 where the Bitlocker Status is enable and also if recovery keys are not found.
I already got a script by Powershell to save the recovery key in AD that I want to deploy for this computers.

We already have a GPO for that but some computers in the Active Directory don't have the recovery key.
I want to make sure that we saved all the recovery keys in AD by forcing to back up the recovery key.

Hope guys can help me.



Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End Like 'ON' And tsysOS.OSname Like 'Win 10'
Order By tblAssets.AssetName


Here's one i created by pulling bits and pieces from here and there.
It shows if the TPM is enabled in bios, if bitlocker is enabled, if the key is in AD (it can take 24 hours for that to update), if the GPO is applied and if secure boot is enabled.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  Case
    When SubQuery2.IsEnabled_InitialValue = 1 Then 'Yes'
    Else 'No'
  End As [TPM Enabled in BIOS],
  Case
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'Yes'
    Else 'No'
  End As [Bitlocker Enabled],
  Case
    When
      tblADComputers.ADObjectID Not In (Select
          tblBitLockerRecoveryKey.AdObjectId
        From tblBitLockerRecoveryKey) Then 'No'
    Else 'Yes'
  End As [Key In AD],
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End As [GPO Applied],
  Case
    When SubQuery3.Valuename Is Not Null And SubQuery3.Value <> '' Then 'Yes'
    Else 'No'
  End As [SecureBoot Enabled],
  Case
    When tblPortableBattery.AssetID Is Null Then 'Desktop'
    Else 'Laptop'
  End As [Desktop/Laptop],
  tblAssets.Domain,
  tblAssets.OScode + '.' + tblAssets.BuildNumber As Build,
  tblAssets.Version As [OS Version],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysIPLocations.IPLocation As IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  TsysLastscan.Lasttime As LastRegistryScan,
  SubQuery1.Regkey,
  SubQuery1.Valuename,
  SubQuery1.Value,
  SubQuery1.Lastchanged,
  Case
    When TsysLastscan.Lasttime < GetDate() - 1 Then
      'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.'
  End As Comment,
  SubQuery2.SpecVersion,
  SubQuery2.IsEnabled_InitialValue,
  tblAssetCustom.State,
  SubQuery3.Regkey As Regkey1,
  SubQuery3.Valuename As Valuename1,
  SubQuery3.Value As Value1
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where tblRegistry.Regkey Like '%SOFTWARE\Policies\Microsoft\FVE' And
      tblRegistry.Valuename = 'OSActiveDirectoryBackup') SubQuery1 On
      SubQuery1.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Lastchanged,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Control\SecureBoot\State' And
      tblRegistry.Valuename = 'UEFISecureBootEnabled' And tblRegistry.Value =
      '1') SubQuery3 On SubQuery3.AssetID = tblAssets.AssetID
  Left Join lansweeperdb.dbo.tblPortableBattery On tblAssets.AssetID =
      tblPortableBattery.AssetID
  Inner Join lansweeperdb.dbo.tblADComputers On tblAssets.AssetID =
      tblADComputers.AssetID
  Inner Join lansweeperdb.dbo.tsysAssetTypes On tsysAssetTypes.AssetType =
      tblAssets.Assettype
  Inner Join lansweeperdb.dbo.tblEncryptableVolume On tblAssets.AssetID =
      tblEncryptableVolume.AssetId
  Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Join (Select Top 1000000 tblTPM.AssetId,
      tblTPM.SpecVersion,
      tblTPM.IsEnabled_InitialValue
    From tblTPM) SubQuery2 On SubQuery2.AssetId = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblEncryptableVolume.DriveLetter = 'C:' And
  TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
  tblAssets.AssetName
0 Kudos
Asoumpholphakdy
Engaged Sweeper II
In response to Powen

‎02-23-2022 11:34 AM

Hello Powen.cheng;

Try with this :


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.OScode + '.' + tblAssets.BuildNumber As Build,
tblAssets.Version As [OS Version],
TsysLastscan.Lasttime As LastRegistryScan
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%SOFTWARE\Policies\Microsoft\FVE' And
tblRegistry.Valuename = 'OSActiveDirectoryBackup') SubQuery1 On
SubQuery1.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Lastchanged,
tblRegistry.Value
From tblRegistry
Where
tblRegistry.Regkey Like
'%SYSTEM\CurrentControlSet\Control\SecureBoot\State' And
tblRegistry.Valuename = 'UEFISecureBootEnabled' And tblRegistry.Value =
'1') SubQuery3 On SubQuery3.AssetID = tblAssets.AssetID
Left Join lansweeperdb.dbo.tblPortableBattery On tblAssets.AssetID =
tblPortableBattery.AssetID
Inner Join lansweeperdb.dbo.tblADComputers On tblAssets.AssetID =
tblADComputers.AssetID
Inner Join lansweeperdb.dbo.tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join lansweeperdb.dbo.tblEncryptableVolume On tblAssets.AssetID =
tblEncryptableVolume.AssetId
Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join (Select Top 1000000 tblTPM.AssetId,
tblTPM.SpecVersion,
tblTPM.IsEnabled_InitialValue
From tblTPM) SubQuery2 On SubQuery2.AssetId = tblAssets.AssetID
Where Case
When tblEncryptableVolume.ProtectionStatus = 1 Then 'Yes'
Else 'No'
End Like 'Yes' And tblAssetCustom.State = 1 And
tblEncryptableVolume.DriveLetter = 'C:' And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName





Powen.cheng wrote:
HI looktall,

Excuse me, if I only need to find out the computer with bitlocker enabled, how can I modify the report? thanks




looktall wrote:
Tony S. wrote:
Hi everybody,

First, thanks for all the help I found before in the forum for what I needed.
Now I'm lil bit stuck and ask for your help.

I try to do my own report but not really working 😕 I don't know SQL but still trying to learn when I got time.

I need a report that shows my computers with Windos 10 where the Bitlocker Status is enable and also if recovery keys are not found.
I already got a script by Powershell to save the recovery key in AD that I want to deploy for this computers.

We already have a GPO for that but some computers in the Active Directory don't have the recovery key.
I want to make sure that we saved all the recovery keys in AD by forcing to back up the recovery key.

Hope guys can help me.



Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End Like 'ON' And tsysOS.OSname Like 'Win 10'
Order By tblAssets.AssetName


Here's one i created by pulling bits and pieces from here and there.
It shows if the TPM is enabled in bios, if bitlocker is enabled, if the key is in AD (it can take 24 hours for that to update), if the GPO is applied and if secure boot is enabled.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  Case
    When SubQuery2.IsEnabled_InitialValue = 1 Then 'Yes'
    Else 'No'
  End As [TPM Enabled in BIOS],
  Case
    When tblEncryptableVolume.ProtectionStatus = 1 Then 'Yes'
    Else 'No'
  End As [Bitlocker Enabled],
  Case
    When
      tblADComputers.ADObjectID Not In (Select
          tblBitLockerRecoveryKey.AdObjectId
        From tblBitLockerRecoveryKey) Then 'No'
    Else 'Yes'
  End As [Key In AD],
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End As [GPO Applied],
  Case
    When SubQuery3.Valuename Is Not Null And SubQuery3.Value <> '' Then 'Yes'
    Else 'No'
  End As [SecureBoot Enabled],
  Case
    When tblPortableBattery.AssetID Is Null Then 'Desktop'
    Else 'Laptop'
  End As [Desktop/Laptop],
  tblAssets.Domain,
  tblAssets.OScode + '.' + tblAssets.BuildNumber As Build,
  tblAssets.Version As [OS Version],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysIPLocations.IPLocation As IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  TsysLastscan.Lasttime As LastRegistryScan,
  SubQuery1.Regkey,
  SubQuery1.Valuename,
  SubQuery1.Value,
  SubQuery1.Lastchanged,
  Case
    When TsysLastscan.Lasttime < GetDate() - 1 Then
      'Last registry scan more than 24 hours ago! Scanned registry information may not be up-to-date. Try rescanning this machine.'
  End As Comment,
  SubQuery2.SpecVersion,
  SubQuery2.IsEnabled_InitialValue,
  tblAssetCustom.State,
  SubQuery3.Regkey As Regkey1,
  SubQuery3.Valuename As Valuename1,
  SubQuery3.Value As Value1
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where tblRegistry.Regkey Like '%SOFTWARE\Policies\Microsoft\FVE' And
      tblRegistry.Valuename = 'OSActiveDirectoryBackup') SubQuery1 On
      SubQuery1.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Lastchanged,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Control\SecureBoot\State' And
      tblRegistry.Valuename = 'UEFISecureBootEnabled' And tblRegistry.Value =
      '1') SubQuery3 On SubQuery3.AssetID = tblAssets.AssetID
  Left Join lansweeperdb.dbo.tblPortableBattery On tblAssets.AssetID =
      tblPortableBattery.AssetID
  Inner Join lansweeperdb.dbo.tblADComputers On tblAssets.AssetID =
      tblADComputers.AssetID
  Inner Join lansweeperdb.dbo.tsysAssetTypes On tsysAssetTypes.AssetType =
      tblAssets.Assettype
  Inner Join lansweeperdb.dbo.tblEncryptableVolume On tblAssets.AssetID =
      tblEncryptableVolume.AssetId
  Inner Join lansweeperdb.dbo.tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Join (Select Top 1000000 tblTPM.AssetId,
      tblTPM.SpecVersion,
      tblTPM.IsEnabled_InitialValue
    From tblTPM) SubQuery2 On SubQuery2.AssetId = tblAssets.AssetID
Where tblAssetCustom.State = 1 And tblEncryptableVolume.DriveLetter = 'C:' And
  TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
  tblAssets.AssetName


New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now