cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Asoumpholphakdy
Engaged Sweeper II
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
4 REPLIES 4
Asoumpholphakdy
Engaged Sweeper II
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.
looktall
Engaged Sweeper III
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
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
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