SCCM SQL Query to Find Machines Impacted by AMT Firmware Exploit (INTEL-SA-00075)

2017-05-02 SQL ConfigMgr

Share on:

The following SQL should help identify the state of impacted systems. Please review thoroughly before relying on this information, while I believe the results should be accurate, you should do your own due diligence. Please let me know if you find any errors or have any suggestions to return better data.

More information on INTEL-SA-00075 here.

 1select v_r_system.Name0
 2     , v_GS_COMPUTER_SYSTEM.Manufacturer0
 3	 , v_GS_COMPUTER_SYSTEM.Model0
 4	 , v_GS_COMPUTER_SYSTEM_PRODUCT.Version0 as SystemProductVersion
 5	 , v_GS_PC_BIOS.SerialNumber0
 6     , v_gs_workstation_status.lasthwscan
 7     , v_r_system.operating_system_name_and0
 8	 , v_GS_AMT_AGENT.AMT0
 9	 , v_GS_AMT_AGENT.BuildNumber0
10	 , case when v_GS_AMT_AGENT.ProvisionMode0 = 1 then 'Enteprise Provisioning Mode'
11	        when v_GS_AMT_AGENT.ProvisionMode0 = 2 then 'Small Business Provisioning Mode'
12	        else 'Unknown (' + cast(v_GS_AMT_AGENT.ProvisionMode0 as varchar) + ')'
13	   end as ProvisionMode /* https://msdn.microsoft.com/en-us/library/dd339697.aspx */
14	 , case when v_GS_AMT_AGENT.ProvisionState0 = 0 then 'Factory Setup Mode'
15	        when v_GS_AMT_AGENT.ProvisionState0 = 1 then 'Set-up Mode'
16			when v_GS_AMT_AGENT.ProvisionState0 = 2 then 'Operational Mode'
17	        else 'Unknown (' + cast(v_GS_AMT_AGENT.ProvisionState0 as varchar) + ')'
18	   end as ProvisionState /* https://msdn.microsoft.com/en-us/library/dd339697.aspx */
19	 , case when v_GS_AMT_AGENT.AMT0 like '6.0.%' or v_GS_AMT_AGENT.AMT0 like '6.1.%' or v_GS_AMT_AGENT.AMT0 like '6.2.%' or v_GS_AMT_AGENT.AMT0 like '7.0.%' or v_GS_AMT_AGENT.AMT0 like '7.1.%' or v_GS_AMT_AGENT.AMT0 like '8.0.%' or v_GS_AMT_AGENT.AMT0 like '8.1.%' or v_GS_AMT_AGENT.AMT0 like '9.0.%' or v_GS_AMT_AGENT.AMT0 like '9.1.%' or v_GS_AMT_AGENT.AMT0 like '9.5.%' or v_GS_AMT_AGENT.AMT0 like '10.0.%' or v_GS_AMT_AGENT.AMT0 like '11.0.%' or v_GS_AMT_AGENT.AMT0 like '11.5.%' or v_GS_AMT_AGENT.AMT0 like '11.6.%' then
20			case when v_GS_AMT_AGENT.BuildNumber0 < 3000 then 'Impacted - Unresolved (Build < 3000)'
21			     else 'Impacted - Resolved (Build >= 3000)'
22		    end
23	        else 'Not an impacted version'
24	   end as Impact /* https://security-center.intel.com/advisory.aspx?intelid=INTEL-SA-00075&languageid=en-fr */
25  from v_r_system
26  join v_GS_AMT_AGENT
27    on v_r_system.resourceid = v_GS_AMT_AGENT.ResourceID
28  left join v_gs_workstation_status
29    on v_r_system.resourceid = v_gs_workstation_status.resourceid
30  left join v_gs_computer_system
31    on v_r_system.resourceid = v_gs_computer_system.resourceid
32  left join v_GS_PC_BIOS
33    on v_r_system.resourceid = v_GS_PC_BIOS.ResourceID
34  left join v_GS_COMPUTER_SYSTEM_PRODUCT
35    on v_r_system.resourceid = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
36 where v_gs_workstation_status.lasthwscan >= GetDate()-30