Q: What SQL command can I use against the System Center Configuration Manager (SCCM) database to view all machines that require patches?

A: The SQL code below is based on the reports that are part of Configuration Manager, which will show machines that require patches.

 

SELECT
b.Netbios_Name0 AS Name,
CASE a.STATUS WHEN 2 THEN 'Required' WHEN 3 THEN 'Installed' END AS STATUS,
c.ArticleID,
c.daterevised AS ReleasedDate,
c.Title
FROM v_UpdateComplianceStatus AS a, v_R_System AS b, v_UpdateInfo AS c
WHERE
a.ResourceID = b.ResourceID AND
a.CI_ID = c.CI_ID AND
c.ArticleID IS NOT NULL AND
a.STATUS = 2
ORDER BY b.Netbios_Name0