Jump to content


w116tjb

Custom Report - Security Updates Deployed But Still Required

Recommended Posts

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.

gI9TG1h.jpg

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.