How to query Infineon firmware TPM (Microsoft Advisory ADV170012) in ConfigMgr

If you don’t know what is this about, you must read this https://portal.msrc.microsoft.com/en-US/security-guidance/advisory/ADV170012

Additional, in this article https://support.microsoft.com/en-us/help/4046783/bitlocker-mitigation-plan-for-vulnerability-in-tpm, it said 

When TPM-based protector is used to protect the operating system volume, the security of the BitLocker protection is affected only if the TPM firmware version is 1.2.

But the script what Microsoft provided doesn’t include detection if TPM firmware version is 1.2 or not.

I found two excellent posts how to get those information out using PowerShell and ConfigMgr compliance settings
https://p0w3rsh3ll.wordpress.com/2017/10/13/about-microsoft-advisory-adv170012/
https://www.imab.dk/detect-vulnerability-in-tpm-adv170012-using-configmgr-compliance-settings/

If you have Windows 7, and didn’t update Windows Management Framework 5.0 or 5.1 yet, you can’t use Get-TPM, but you can use this instead.

Anyway, my co-worker Bamberg Antti figured we can use SQL query those information from ConfigMgr, and of course you should have hardware inventory enable for Win32_TPM. 

Antti and me modified the SQL and WQL query so that I can put them in this post, please modify as your own needs.

For SQL reporting (this will give results for safe and not safe Infineon firmware TPM):

(as note for myself, this a nice website that can make query format looks better http://poorsql.com/  🙂 )

;

WITH 
ase
AS (
	SELECT SUBSTRING(ManufacturerVersion0, 1, CHARINDEX('.', ManufacturerVersion0, 1) - 1) AS major,
		SUBSTRING(ManufacturerVersion0, CHARINDEX('.', ManufacturerVersion0, 1) + 1, LEN(ManufacturerVersion0) - CHARINDEX('.', ManufacturerVersion0, 1)) AS minor,
		resourceid,
		ManufacturerVersion0,
		ManufacturerId0,
		PhysicalPresenceVersionInfo0,
		SpecVersion0
	FROM v_GS_TPM
	WHERE ManufacturerId0 = 0x49465800
	),

ase1
AS (
	SELECT *
	FROM ase
	WHERE major IN (4, 5, 6, 7, 133)
	),

ase2
AS (
	SELECT ase1.*,
		CASE WHEN major = 4 AND (minor < 33 OR (minor >= 40 AND minor <= 42)) THEN 'not safe' WHEN major = 5 AND minor <= 61 THEN 'not safe' WHEN major = 6 AND minor <= 42 THEN 'not safe' WHEN major = 7 AND minor <= 61 THEN 'not safe' WHEN major = 133 AND minor <= 32 THEN 'not safe' ELSE 'safe' END AS firmwarecheck
	FROM ase1
	),

FINAL
AS (
	SELECT name0 AS MachineName,
		DisplayName,
		UserName,
		ase2.*,
		Manufacturer,
		Model,
		CASE WHEN Manufacturer = 'Lenovo' THEN model2 ELSE model END AS ModelFriendlyName,
		OSBUILDNro,
		bios,
		biosdate,
		LastMessageSentTime
	FROM ase2
	LEFT JOIN v_R_System sys ON sys.resourceid = ase2.ResourceID
	OUTER APPLY (
		SELECT manufacturer0 AS Manufacturer,
			model0 AS model
		FROM v_GS_COMPUTER_SYSTEM cs
		WHERE sys.resourceid = cs.resourceid
		) AS comp
	OUTER APPLY (
		SELECT version0 AS model2
		FROM v_GS_COMPUTER_SYSTEM_PRODUCT csp
		WHERE sys.resourceid = csp.resourceid
		) AS prod
	OUTER APPLY (
		SELECT version0 AS OSBUILDNro
		FROM v_GS_OPERATING_SYSTEM AS os
		WHERE sys.resourceid = os.resourceid
		) AS osb
	OUTER APPLY (
		SELECT NAME
		FROM vsms_windowsservicingstates B
		WHERE B.build = sys.build01 AND B.branch = sys.osbranch01
		) AS ws
	OUTER APPLY (
		SELECT value
		FROM vsms_windowsservicinglocalizednames C
		WHERE ws.NAME = c.NAME
		) AS ws2
	OUTER APPLY (
		SELECT SMBIOSBIOSVERSION0 AS bios,
			Releasedate0 AS biosdate
		FROM v_GS_PC_BIOS pcbios
		WHERE pcbios.ResourceID = sys.ResourceID
		) AS citi
	OUTER APPLY (
		SELECT displayName0 AS DisplayName,
			user_name0 AS UserName
		FROM v_R_user us
		WHERE us.user_name0 = sys.User_Name0
		) AS dname
	OUTER APPLY (
		SELECT cms.LastMessageSentTime
		FROM dbo.v_ClientMessageStatistics cms
		WHERE cms.ResourceID = sys.ResourceID
		) AS cms
	)

SELECT * FROM FINAL

You should get a results like this, or let’s hope you don’t get any results (means everything is fine)

WQL query for ConfigMgr Monitor (this only give results for not safe Infineon firmware TPM):

SELECT DISTINCT SMS_R_System.NetbiosName,
	SMS_G_System_TPM.ManufacturerId,
	SMS_G_System_TPM.ManufacturerVersion,
	SMS_G_System_TPM.PhysicalPresenceVersionInfo,
	SMS_G_System_TPM.SpecVersion
FROM SMS_R_System
INNER JOIN SMS_G_System_TPM
	ON SMS_G_System_TPM.ResourceID = SMS_R_System.ResourceId
WHERE SMS_G_System_TPM.ManufacturerId = 1229346816
	AND (
		SMS_G_System_TPM.ManufacturerVersion LIKE "4%"
		AND (
			SMS_G_System_TPM.ManufacturerVersion <= "4.33"
			OR SMS_G_System_TPM.ManufacturerVersion >= "4.40"
			AND SMS_G_System_TPM.ManufacturerVersion <= "4.42"
			)
		OR SMS_G_System_TPM.ManufacturerVersion LIKE "5%"
		AND SMS_G_System_TPM.ManufacturerVersion <= "5.61"
		OR SMS_G_System_TPM.ManufacturerVersion LIKE "6%"
		AND SMS_G_System_TPM.ManufacturerVersion <= "6.42"
		OR SMS_G_System_TPM.ManufacturerVersion LIKE "7%"
		AND SMS_G_System_TPM.ManufacturerVersion <= "7.61"
		OR SMS_G_System_TPM.ManufacturerVersion LIKE "133%"
		AND SMS_G_System_TPM.ManufacturerVersion <= "133.32"
		)

WQL Query for ConfigMgr collection (this only give results for not safe Infineon firmware TPM):

SELECT SMS_R_SYSTEM.ResourceID,
	SMS_R_SYSTEM.ResourceType,
	SMS_R_SYSTEM.NAME,
	SMS_R_SYSTEM.SMSUniqueIdentifier,
	SMS_R_SYSTEM.ResourceDomainORWorkgroup,
	SMS_R_SYSTEM.Client
FROM SMS_R_System
INNER JOIN SMS_G_System_TPM
	ON SMS_G_System_TPM.ResourceID = SMS_R_System.ResourceId
WHERE SMS_G_System_TPM.ManufacturerId = 1229346816
	AND (
		SMS_G_System_TPM.ManufacturerVersion LIKE "4%"
		AND (
			SMS_G_System_TPM.ManufacturerVersion <= "4.33"
			OR SMS_G_System_TPM.ManufacturerVersion >= "4.40"
			AND SMS_G_System_TPM.ManufacturerVersion <= "4.42"
			)
		OR SMS_G_System_TPM.ManufacturerVersion LIKE "5%"
		AND SMS_G_System_TPM.ManufacturerVersion <= "5.61"
		OR SMS_G_System_TPM.ManufacturerVersion LIKE "6%"
		AND SMS_G_System_TPM.ManufacturerVersion <= "6.42"
		OR SMS_G_System_TPM.ManufacturerVersion LIKE "7%"
		AND SMS_G_System_TPM.ManufacturerVersion <= "7.61"
		OR SMS_G_System_TPM.ManufacturerVersion LIKE "133%"
		AND SMS_G_System_TPM.ManufacturerVersion <= "133.32"
		)