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 Clearing Thermal State Warning
by  calmed-anodises   Go to last post Go to first unread
Last post: 5/20/2022 6:28:27 PM(UTC)
Lansweeper Quality problems with Lansweeper
by  Hendrik.VE  
Go to last post Go to first unread
Last post: 5/20/2022 3:43:44 PM(UTC)
Lansweeper Bitlocker keys
by  SWResearch   Go to last post Go to first unread
Last post: 5/20/2022 1:34:18 PM(UTC)
Lansweeper Lansweeper Helpdesk instructional webinar
by  Erik.T  
Go to last post Go to first unread
Last post: 5/20/2022 10:40:30 AM(UTC)
Lansweeper Outlook task and calendar
by  ITVTECH   Go to last post Go to first unread
Last post: 5/20/2022 8:26:51 AM(UTC)
Lansweeper SCCM scanning incorrectly reporting last seen results
by  SWResearch  
Go to last post Go to first unread
Last post: 5/20/2022 12:34:52 AM(UTC)
Lansweeper Scanning certificates
by  EagleEyeJoe   Go to last post Go to first unread
Last post: 5/19/2022 7:39:24 PM(UTC)
Lansweeper Lansweeper Cloud Location
by  FrankSc  
Go to last post Go to first unread
Last post: 5/19/2022 7:07:49 PM(UTC)