Friday, September 6, 2013

SCCM Patch Compliance - Listing required outstanding patches by server

The below query will enable you to get the patches that are required but has yet to be installed by server.
Replace the parameter @machinename with the server that you are referencing against.

declare @RscID int;
select @RscID=ResourceID from v_R_System where ((Name0 = @machinename) and (Active0 = 1));

catinfo2.CategoryInstanceName as UpdateClassification,
            ui.BulletinID as BulletinID,
            ui.ArticleID as ArticleID,
            ui.Title as Title,          
ui.InfoURL as InformationURL
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
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'
left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
left join (
                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
where  css.ResourceID = @RscID
and ((css.Status=2) )
order by catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID

Output of the report will be in the format as below when performed via SSRS

No comments:

Post a Comment