Tuesday, September 17, 2013

SCCM Patch Compliance Report for Machines by Wildcard

As an add on to a previous post I had for the patch compliance, the below query is to do the same thing but that it will allow you to list the all machines that you require information as well as using a wildcard (i.e for machine name starting with ABC change the wildcard underlined to 'ABC%')

select
   VRS.Name0,
   catinfo2.CategoryInstanceName as UpdateClassification,
            ui.BulletinID as BulletinID,
            ui.DatePosted,
   ui.DateRevised,        
            ui.ArticleID as ArticleID,
            ui.Title as Title,        
   ui.InfoURL as InformationURL
from v_UpdateComplianceStatus css
left join v_R_System VRS on VRS.ResourceID = css.ResourceID
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 = VRS.ResourceID
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 vrs.Active0 = 1and css.Status=2 and VRS.Name0 like '%'
order by VRS.name0 ,catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID

No comments:

Post a Comment