SQL Query : All Computers Missing a Specific KB Update

If you identify a software update which has been applied to a limited number of your devices, this query can be used to identify those where the KB is missing.

select sys.name0 [Computername],
  osd.OSLanguage00 [OS Language],
  osd.Caption00 [OS Name]
from v_updateinfo ui
  inner join v_UpdateComplianceStatus ucs on ucs.ci_id=ui.ci_id
  join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID
  join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'
  join v_R_System sys on sys.resourceid=ucs.resourceid
  join Operating_System_DATA osd on sys.ResourceID = osd.MachineID
   and ucs.status='2' -- required
   AND ui.articleid='3012973'
  order by sys.name0

Sample Output

 

Related posts

One Thought to “SQL Query : All Computers Missing a Specific KB Update”

  1. henry

    here is an sql query that direcly queries the hotfixes and related hostnames from SCCM database
    https://bestitsm.wordpress.com/2018/07/12/how-to-get-list-of-installed-hotfixes-from-sccm-database/

Leave a Comment