SCCM Configmgr SQL Query How to get list of patches required by specific Computer


There are instances (you might want to get list of required patches by server ,so can download and install in DMZ who do not have internet or for patch compliance checking for specific computer and many) , where you might be searching for list patches of required by specific computer/computers and generally ,we look at default reports if there is any report which can give me the required information.

If you look at default reports,there is no exact report give you the list of patches required by specific computer with targeted,required,when was it released and other information.

You can use below SQL Query to run  from SQL SERVER MANAGEMENT STUDIO or use this in your SSRS Reports with parameter for computer name.

This query has been customized to present the Month posted,Title of the patch,Targeted to the client,Required ,KB URL,Date posted and deadline if there is any.

Note: This Query is filtered with Severity (Security and critical)  from view v_UpdateInfo.

SQL Code:

select CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
ui.bulletinid [BulletinID],ui.articleid [ArticleID], ui.Title,
    Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
    IsRequired=(case when css.Status=2 then '*' else '' end),
    ui.InfoURL as InformationURL,
    ui.dateposted [Date Posted] ,
    from V_UpdateComplianceStatus  css
    join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
    left join v_CITargetedMachines  ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
    INNER join v_CICategories_All catall2 on catall2.CI_ID=css.CI_ID
    INNER  join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID
    and catinfo2.CategoryTypeName='UpdateClassification'
    JOIN dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID
       outer apply (
       select Deadline=min(a.EnforcementDeadline)
       from v_CIAssignment  a
       join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
       ) cdl
   WHERE vrs.Name0='Win7X64-001' and
   ui.Severity IN (8, 10) --this is for security and critical updates
  AND css.Status=2  --for required

I posted the same query on TechNet forums ,more info

14 Responses to "SCCM Configmgr SQL Query How to get list of patches required by specific Computer"

  1. Hi Eswar, this looks very useful, is there a way of just producing the count of patches that are missing for devices in a Collection. We are just starting to use SCCM to do patching on our server estate and have a requirement to see how far out of date each server is so that we can estimate the time required to update them.

  2. Eswar
    I think i got it wrong. (Management 2 - Updates required but not deployed) report might be the one i needed after all.
    I think the NOT DEPLOYED means the updates that is not deployed to the collection that i am running the report against. Is that correct? Thanks, Dave.

  3. Hi Eswar
    Another request for you.
    Is it possible to modify this and have a report that list all required updates (whether deployed or not, or downloaded or not), for a collection? Very similar to the built in report (Management 2 - Updates required but not deployed). But, not limiting it to "Not deployed".
    Thanks again, DM.


Leave a Reply