Report: Not Encrypted

[MHScripts]

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

[MHScripts]

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 · Accepted solution]

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]

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.