Notification

Icon
Error

Report to get computers with Bitlocker enable and Recovery Keys empty

Posted: Monday, January 17, 2022 4:27:17 PM(UTC)
Tony S.

Tony S.

Member Original PosterPosts: 5
0
Like
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
looktall
#1looktall Member Posts: 18  
posted: 1/19/2022 9:50:52 AM(UTC)
Originally Posted by: Tony S. Go to Quoted Post
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.

Code:
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
Tony S.
#2Tony S. Member Original PosterPosts: 5  
posted: 1/19/2022 10:28:36 AM(UTC)
Hello Looktall,

Thanks for your help Dancing Applause
That's the perfect thing I was looking for
Hope it can help other people who needed it too.
Powen.cheng
#3Powen.cheng Member Posts: 6  
posted: 2/23/2022 6:59:04 AM(UTC)
HI looktall,

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




Originally Posted by: looktall Go to Quoted Post
Originally Posted by: Tony S. Go to Quoted Post
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.

Code:
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
Tony S.
#4Tony S. Member Original PosterPosts: 5  
posted: 2/23/2022 11:34:39 AM(UTC)
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





Originally Posted by: Powen.cheng Go to Quoted Post
HI looktall,

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




Originally Posted by: looktall Go to Quoted Post
Originally Posted by: Tony S. Go to Quoted Post
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.

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


Active Discussions

Lansweeper mail reports
by  Andy.S   Go to last post Go to first unread
Last post: 7/1/2022 2:38:18 PM(UTC)
Lansweeper Adding an "Employee ID" column to an asset report
by  ABaker  
Go to last post Go to first unread
Last post: 6/30/2022 3:06:54 PM(UTC)
Lansweeper Firefox 102 & ESR 91.11
by  Esben.D   Go to last post Go to first unread
Last post: 6/30/2022 8:12:07 AM(UTC)
Lansweeper Performance report not shows result
by  NoraD  
Go to last post Go to first unread
Last post: 6/28/2022 7:52:27 AM(UTC)
Lansweeper Duplicate AD Users
by  Randy Costa   Go to last post Go to first unread
Last post: 6/27/2022 5:25:04 PM(UTC)
Lansweeper Multiple Devices Owned by Users (asset relations)
by  Adrian Scott  
Go to last post Go to first unread
Last post: 6/22/2022 5:34:51 PM(UTC)