Custom Report - Security Updates Deployed But Still Required

[w116tjb]

I'm trying to generate a custom report in SCCM 2012 R2 but am having a bit of trouble getting things working. The report needs to show all Security Updates that have been Deployed but are still showing as Required on servers. I would like it to have a separate entry for each applicable security update and the server that it is applicable to. When I've been searching for how to do this, a lot of people have said that the report will be so big that it won't be usable, but this is for a vulnerability remediation tracking document and it's exactly the format that we need. For the most part, our environment is patched up to date, so the report should have about 1000 lines.

 

Here is an example of what I'm looking for from a formatting perspective.

[w116tjb]

Answered my own question.

 

This will pull all Security Updates that have been Deployed but are still Required (not installed) for the All Systems collection (SMS00001). You can directly modify the code to limit it to the All Servers or All Workstations depending on your site code. You can also change the CategoryInstanceName filter to see different Update Classifications.

select distinct
sys.Name0,
ui.BulletinID as BulletinID,
ui.ArticleID as ArticleID,
ui.Title as Title,
catinfo.CategoryInstanceName as Vendor,
catinfo2.CategoryInstanceName as UpdateClassification,
CASE ((ui.IsDeployed))
  When 0 Then 'No'
  Else 'Yes' End as 'Deployed',
CASE((ui.Severity))
  When 2 Then 'Low'
  When 6 Then 'Moderate'
  When 8 Then 'Important'
  When 10 Then 'Critical'
  Else 'NA' End as 'Severity'
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_R_System sys on css.ResourceID=sys.ResourceID
join v_ClientCollectionMembers ccm on ccm.ResourceID=sys.ResourceID
join v_CICategories_All catall on catall.CI_ID=ui.CI_ID
join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company'
join v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'
where css.Status=2
and ccm.CollectionID='SMS00001'
and ui.isDeployed=1
and catinfo2.CategoryInstanceName='Security Updates'
and catinfo.CategoryInstanceName = 'Microsoft'
order by sys.Name0, ui.ArticleID