Overall Patching Compliance for last 90 days
Question
Need to modify the attached SQL query to get the SCCM patch compliance for a SUG against a collection for last 90 days. Currently it is getting calculated for all the patches in SUG. I neee to restrict the DatePosted to greater than last 90 days. Can someone please help.
Answers ( 7 )
Hopefully, you might have already resolved this issue. Closing the ticket. Feel free to comment if you have a feedback.
Hello Yogesh! Did you check this – SCCM CMPivot Query Patches Installed in Last 90 Days –
+ https://www.anoopcnair.com/sccm-cmpivot-query-patches-installed-in-last-90-days-configmgr/
SCCM Patch Status SQL Query Based on Particular Collection | ConfigMgr
+ https://www.anoopcnair.com/sccm-patch-status-sql-query-based-collection/
The added screenshot is not visible, You can use https://snipboard.io/ to post image for any further queries.
Hello Jitesh,
Thank you for your answer but my requirement is not to find the missing patch or rather I have created another report for that but I would like to know how the below SQL query can be modified to consider only last 90 days patches. The below query calculates the Overall Patching Compliance in Percentage which takes SUG vs Collection as input but the problem is it considers all the patches in SUG and i am looking to restrict that to last 90 days. Would you be able to help please.
select
count (*) [Total Clients],li.title,coll.name,
SUM (CASE WHEN ucs.status=3 or ucs.status=1 then 1 ELSE 0 END ) as ‘Installed / Not Applicable’,
sum( case When ucs.status=2 Then 1 ELSE 0 END ) as ‘Required’,
sum( case When ucs.status=0 Then 1 ELSE 0 END ) as ‘Unknown’,
round((CAST(SUM (CASE WHEN ucs.status=3 or ucs.status=1 THEN 1 ELSE 0 END) as float)/count(*) )*100,2) as ‘Success %’,
round((CAST(count(case when ucs.status not in(‘3′,’1’) THEN ‘*’ end) as float)/count(*))*100,2) as ‘Not Success%’
From v_Update_ComplianceStatusAll UCS
inner join v_r_system sys on ucs.resourceid=sys.resourceid
inner join v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
inner join v_collection coll on coll.collectionid=fcm.collectionid
inner join v_AuthListInfo LI on ucs.ci_id=li.ci_id
inner join v_Updateinfo ui on ucs.ci_id= ui.CI_ID
where li.title=’SUG’ and coll.name=’COLLECTIONAME’
group by li.title,coll.name order by 1
Hello All,
Can someone please help on the query.
Why dont you use inbuilt Compliance report for which is Compliance 3 – Update Group(Per Update) ?
When you already have list of updates of SUG with date release you can compare it with this report and filter out which are not required.
Also if it is one time activity for you you can do it manually from inbuilt report.But if this is requirement for future as well then you can maintain SUG’s like that like one SUG which includes patches less than 90 days and another one older than 90 days.
Nope. we already have SUG align to ADR and the SUG contains patches for more than 180 days. Also, it is not the one time requirement, I want to have a SSRS reports which will calculate the compliance for last 90 days.
Using Compliance 3 – Update Group(Per Update) does not meets my requirement since it gives data per update. I am looking at having a report which just gives me the overall patching compliance in Percentage.