SCCM – Security updates Compliance Overview report

view https://anotherblabla.wordpress.com/2012/03/22/sccm-security-updates-compliance-overview-report-v2/ for some enhancements

Here’s some reports that I use to monitor the level of compliance for security updates and this by collection. Also it’s possibe to drill down and see which security updates is missing.

 

thanks to Garth Jones and Anoop for these informations, which has been very helpfull when I have created those reports

http://anoopmannur.wordpress.com/2011/10/12/configmgr-sccm-software-updatepatching-useful-report-on-client-health/

http://smsug.ca/blogs/garth_jones/archive/2009/02/25/patch-compliance-progression-report.aspx

SQLQueries

1- create a report called  Security Updates Detail.

Report Sql Query

*********************************************************

declare @RscID int;

select @RscID=ResourceID from v_R_System where ((Name0 = @MachineName) and (Active0 = 1));

select

catinfo.CategoryInstanceName as Vendor,

catinfo2.CategoryInstanceName as UpdateClassification,

ui.BulletinID as BulletinID,

ui.ArticleID as ArticleID,

ui.Title as Title,

Targeted=(case when ctm.ResourceID is not null then ‘*’ else ” end),

Installed=(case when css.Status=3 then ‘*’ else ” end),

IsRequired=(case when css.Status=2 then ‘*’ else ” end),

ui.CI_UniqueID as UniqueUpdateID,

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 and catinfo2.CategoryInstanceName like ‘Security Updates’

order by catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID

*********************************************************

Prompt

Name = MachineName

Sql Query

begin

if (@__filterwildcard = ”)

select distinct Name0 from v_R_System WHERE isnull(Obsolete0,0)<>1 order by Name0

else

select distinct Name0 from v_R_System

WHERE Name0 like @__filterwildcard

and isnull(Obsolete0,0)<>1 order by Name0

end

*********************************************************

2- Create a report called Software Updates Compliance Status by Collection and configure the link to the report created in step 1

Report SQL Query

*********************************************************

SELECT

rs.Netbios_name0 as ‘PC Name’,

case

when (sum(case when UCS.status=2 then 1 else 0 end))>0 then (cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ‘ Security Updates Missing’)

else ‘Good Client’

end as ‘Updates Status’,

rs.User_Name0 as ‘Last Logged On User’,

asite.SMS_Assigned_Sites0 as ‘SMS Site’,

rs.Client_Version0 as ‘Client Version’,

ws.lasthwscan as ‘Last Hardware Scan’,

uss.LastScanPackageLocation as ‘Last Scan Location’,

uss.LastScanPackageVersion as ‘Last Scan Pakage’,

st.StateName as ‘Status’,

Convert(VarChar(10), OS.LastBootUpTime0, 101) ‘Last Boot Date’,

DateDiff(D, OS.LastBootUpTime0, GetDate()) ‘Last Boot (Days)’

from v_ClientCollectionMembers ccm

join v_R_System rs on rs.ResourceID = ccm.ResourceID

left outer join v_UpdateComplianceStatus  UCS on UCS.ResourceID = ccm.ResourceID

join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID

join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=’UpdateClassification’

left join v_RA_System_SMSAssignedSites asite on asite.ResourceID = ccm.ResourceID

left join v_gs_workstation_status ws on ws.resourceid=rs.resourceid

left join v_UpdateScanStatus uss on ws.resourceid = uss.ResourceID

left join v_StateNames st on st.TopicType = 501 and st.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rs.Client_Version0, ‘4.0’),

1)<‘4’) then 7 else isnull(uss.LastScanState, 0) end)

left join v_Gs_Operating_System OS on ws.resourceid = OS.ResourceID

where

ccm.CollectionID=@CollID

and catinfo2.CategoryInstanceName like ‘Security Updates’

Group By

rs.Netbios_name0,

rs.User_Name0 ,

asite.SMS_Assigned_Sites0 ,

rs.Client_Version0,

ws.lasthwscan,

uss.LastScanPackageLocation,

uss.LastScanPackageVersion ,

st.StateName ,

OS.LastBootUpTime0,

*********************************************************

Prompt

Name = CollID

begin

if (@__filterwildcard = ”)

select CollectionID as CollectionID, Name as CollectionName from v_Collection order by Name

else

select CollectionID as CollectionID, Name as CollectionName from v_Collection

WHERE CollectionID like @__filterwildcard or Name like @__filterwildcard

order by Name

end

Advertisements

4 thoughts on “SCCM – Security updates Compliance Overview report

  1. louis

    hello, when i past the code i get the message back Incorrect syntax near ” and Incorrect syntax near ‘cdl’

    what’s wrong?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s