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.

  1. SELECT
  2. b.Netbios_Name0 AS Name,
  3. CASE a.STATUS WHEN 2 THEN 'Required' WHEN 3 THEN 'Installed' END AS STATUS,
  4. c.ArticleID,
  5. c.daterevised AS ReleasedDate,
  6. c.Title
  7. FROM v_UpdateComplianceStatus AS a, v_R_System AS b, v_UpdateInfo AS c
  8. WHERE
  9. a.ResourceID = b.ResourceID AND
  10. a.CI_ID = c.CI_ID AND
  11. c.ArticleID IS NOT NULL AND
  12. a.STATUS = 2
  13. ORDER BY b.Netbios_Name0