→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
MHScripts
Engaged Sweeper II
I am new to this but I can't seem to find a way to do this.
Wondering if there is a way to add a column showing whether or not an asset has a TPM chip?
I also need to add the OS column but I think I can get that.

Thanks!







Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblEncryptableVolume.ProtectionStatus,
tblEncryptableVolume.DriveLetter
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblEncryptableVolume.ProtectionStatus = 0 And
tblEncryptableVolume.DriveLetter = 'C:'

1 ACCEPTED SOLUTION
Esben_D
Lansweeper Employee
Lansweeper Employee
TPM information is stored in tblTPM. However, I believe that if a asset does not have a TPM chip, the table will simply not have data in it. If that is the case, the report below should work. However, I would double check it to be safe.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblEncryptableVolume.ProtectionStatus,
tblEncryptableVolume.DriveLetter,
tblOperatingsystem.Caption As [Operating System],
Case
When tblTPM.IsActivated_InitialValue = 1 Then 'Yes'
Else 'No'
End As [TPM Activated]
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblTPM On tblAssets.AssetID = tblTPM.AssetId
Where tblEncryptableVolume.ProtectionStatus = 0 And
tblEncryptableVolume.DriveLetter = 'C:'

View solution in original post

3 REPLIES 3
MHScripts
Engaged Sweeper II
Sorry to keep kicking a dead horse just wanted to make this information available.
This report NOT show computers that have the TPM disabled in the BIOS since LS uses the Win32_TPM WMI for TPM information.
Esben_D
Lansweeper Employee
Lansweeper Employee
TPM information is stored in tblTPM. However, I believe that if a asset does not have a TPM chip, the table will simply not have data in it. If that is the case, the report below should work. However, I would double check it to be safe.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblEncryptableVolume.ProtectionStatus,
tblEncryptableVolume.DriveLetter,
tblOperatingsystem.Caption As [Operating System],
Case
When tblTPM.IsActivated_InitialValue = 1 Then 'Yes'
Else 'No'
End As [TPM Activated]
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblTPM On tblAssets.AssetID = tblTPM.AssetId
Where tblEncryptableVolume.ProtectionStatus = 0 And
tblEncryptableVolume.DriveLetter = 'C:'
MHScripts
Engaged Sweeper II
Charles.X wrote:
TPM information is stored in tblTPM. However, I believe that if a asset does not have a TPM chip, the table will simply not have data in it. If that is the case, the report below should work. However, I would double check it to be safe.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetName,
tblAssetCustom.Model,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Lastseen,
tblEncryptableVolume.ProtectionStatus,
tblEncryptableVolume.DriveLetter,
tblOperatingsystem.Caption As [Operating System],
Case
When tblTPM.IsActivated_InitialValue = 1 Then 'Yes'
Else 'No'
End As [TPM Activated]
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblTPM On tblAssets.AssetID = tblTPM.AssetId
Where tblEncryptableVolume.ProtectionStatus = 0 And
tblEncryptableVolume.DriveLetter = 'C:'


per https://www.lansweeper.com/forum/yaf_postst14381_All-machines-that-don-t-have-a-TPM.aspx#post49475 it does seem you are correct in, if a machine dose not have a TPM chip it will not have information in tblTPM.