ConfigMgr SQL query to Identify TPM Status

Hello All,

I just started working on few Bitlocker related projects wherein TPM plays a crucial role. To make my work easier I wrote a simple query to get TPM Status from ConfigMgr SQL table. Sharing this with community for their use if needed.

All you need to do is use your SSMS to point it to your ConfigMgr DB and trigger this SQL query.

select SYS.Name0[ComputerName],
case TPM.IsActivated_InitialValue0
when '1' then 'True'
when '0' then 'False'
END [TPM Activated],
case TPM.IsEnabled_InitialValue0
when '1' then 'True'
when '0' then 'False'
END [TPM Enabled],
case TPM.IsOwned_InitialValue0
when '1' then 'True'
when '0' then 'False'
END [TPM Owned],
case TPMS.IsApplicable0
when '1' then 'True'
when '0' then 'False'
END [TPM Applicable],
case TPMS.IsReady0
when '1' then 'True'
when '0' then 'False'
END [TPM Ready],
TPM.SpecVersion0[TPM Version]
from v_R_System SYS
JOIN v_GS_TPM TPM
ON SYS.ResourceID = TPM.ResourceID
JOIN v_GS_TPM_STATUS TPMS
ON TPM.ResourceID = TPMS.ResourceID

This query uses 3 tables and you’ll only be able to see information of computers which has processed their hardware inventory in the past.

Kind Regards,

Praveen