All machines that don't have a TPM

[dfratiani]

Does anyone have a report, similar to the built in: "Computer: Trusted Platform Module" report, that will display all machines that do NOT have a TPM?
Thanks!

[JacobH]

also, TPM generally has to be version 2 or greater (off the top of my head) to be compatible with most disk encryption these days... might want to take that into account as well - it's not just TPM or not, it's < version 2

[JacobH]

i'm working on a TPM report put together from another report I found here on the forum...

this seems to work - HOWEVER!!! if you're looking for bitlocker compliance report in the future, you can't trust Lansweeper's WMI queries for that - there are cases where the protectionstatus is OFF (i.e. the drive is accessed as 'unlocked' in the operating system) but it is actually encrypted and Bitlocker states as such. So yeah, I can't use LS to actually true-up bitlocker.


Here's the TPM report



Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblTPM.SpecVersion,
Case
When tblTPM.IsActivated_InitialValue = 1 Then 'Yes'
When tblTPM.IsActivated_InitialValue Is Null Then Null
Else 'No'
End As Activated,
Case
When tblTPM.IsEnabled_InitialValue = 1 Then 'Yes'
When tblTPM.IsEnabled_InitialValue Is Null Then Null
Else 'No'
End As Enabled,
Case
When tblTPM.IsOwned_InitialValue = 1 Then 'Yes'
When tblTPM.IsOwned_InitialValue Is Null Then Null
Else 'No'
End As Owned,
tsysOS.OSname As OS,
tblAssets.SP,
tblEncryptableVolume.DriveLetter,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblPortableBattery.AssetID Is Null Then 'Desktop'
Else 'Laptop'
End As [Desktop/Laptop],
Case
When tblTPM.AssetId Is Null Then 'noTPM'
Else 'HasTPM'
End As HasTPMorNot
From tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join tblTPM On tblAssets.AssetID = tblTPM.AssetId
Left Join tblEncryptableVolume On
tblAssets.AssetID = tblEncryptableVolume.AssetId
Where (tblAssetCustom.Model Is Null Or tblAssetCustom.Model = '' Or
tblAssetCustom.Model Not Like '%Virtual%') And
tblAssets.Lastseen Is Not Null And tblAssets.Lastseen <> '' And
tblState.Statename = 'Active' And tsysAssetTypes.AssetTypename In ('Windows',
'Windows CE')
Order By tblAssets.Domain,
tblAssets.AssetName

[Nick_VDB · Accepted solution]

The report below will give back any assets that do not have information in the tblTPM table.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
  From tblAssets Inner Join tblTPM On tblAssets.AssetID = tblTPM.AssetId) And
  tblAssetCustom.State = 1