Thursday, September 26, 2013

SQL Report for TPM (Trusted Platform Module)

A TPM is a microchip designed to provide basic security-related functions, primarily involving encryption keys. The TPM is usually installed on the motherboard of a computer or laptop.

To get an SCCM report on TPM status, we need to extend the hardware inventory to get the TPM chip information included in the inventory. Check this myitforum
post for information on extending the inventory.
http://www.myitforum.com/forums/Creating-a-Query-to-determine-TPM-Chip-status-on-Laptops-m209158.aspx


If the above steps are made, then the below given query can be used to get a SQL Report for listing the Laptop names on which on TPM is enabled and/or activated.

Select sys.Name0 as "Machine Name",
sys.AD_Site_Name0 as "Site Name",
TPM.timestamp,
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer,
v_GS_COMPUTER_SYSTEM.Model0 AS Model,
TPM.ManufacturerVersion0 as 'Manufacturer Version',
"TPM_VERSION" = CASE
When TPM.SpecVersion0 in ('1.2, 2, 0', '1.2, 2, 1', '1.2, 2, 2', '1.2, 2, 3')
THEN '1.2'  ELSE 'Null'
END,
"TPM_Activated" = CASE
When TPM.IsActivated_InitialValue0 = 1 THEN 'Yes'
ELSE 'No'
END,
"TPM_Enabled" = CASE
When TPM.IsEnabled_InitialValue0 = 1 THEN 'Yes'
ELSE 'No'
END
from v_GS_TRUSTED_PLATFORM_MODULE TPM
Join v_r_system sys on sys.ResourceID = TPM.ResourceID
inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = tpm.ResourceID)
--where sys.AD_Site_Name0 = @ADSitename
order by "TPM_Activated" asc, "TPM_VERSION" desc, TPM.timestamp 

Ref.:http://social.technet.microsoft.com/Forums/systemcenter/en-US/760c10c5-324a-4e3f-a5e5-5ebdc5b37d3a/create-sccm-report-to-show-tpm-status

No comments:

Post a Comment