SCCM Software Compliance Report Filtered by Update Classification and Update Age

December 15, 2018 November 12, 2020 / By Ben Whitmore / 36 Comments

This SSRS Report has been sitting in the side lines for a while now. After spending a bit more time on it over the weekend I feel it is now at a point where it is serving me rather than the other way around.

Our teams wanted to know how many updates were outstanding for each client, how old the updates were and what the Update Classification was.

The real show stopper was trying to get Update Classification into the report. After finding the correct SQL view things started to fall into place.

SELECT 
CategoryInstanceID, 
CategoryInstanceName 

FROM 
v_CategoryInfo

WHERE 
CategoryTypeName = 'UpdateClassification'

SELECT

CategoryInstanceID,

CategoryInstanceName

FROM

v_CategoryInfo

WHERE

CategoryTypeName = 'UpdateClassification'

Gives the following results:-

16777242 Applications
16777243 Critical Updates
16777244 Definition Updates
16777245 Drivers
16777246 Feature Packs
16777247 Security Updates
16777248 Service Packs
16777249 Tools
16777250 Update Rollups
16777251 Updates
16777252 WSUS Infrastructure Updates
16777466 Upgrades

So we could take this knowledge, and use CategoryInstanceID to filter Update Classifications – cool.

Cutting a long story short, and just putting this post together quickly because i promised to share the query, here it is in SQL Query format.

Modify @COLLID to the collection you want to test against. @COLLID is a parameter in our SSRS Report. We are only setting the variable here so we can return some results in SQL

Set @COLLID = ‘SMS00001’
Modify @COLLID value in your SQL Query

Declare @COLLID VARCHAR(50)
Declare @StartDate30 As Date
Declare @EndDate31 As Date
Declare @StartDate60 As Date
Declare @EndDate0 As Date
Declare @EndDate61 As Date
Declare @StartDateOlder As Date
Declare @EndDate As Date
Declare @StartDate As Date
Declare @EndDate81 As Date
Declare @StartDate80 As Date

Set @StartDate30 = DateAdd("d",-30,GETDATE())
Set @EndDate31 = DateAdd("d",-31,GETDATE())
Set @StartDate60 = DateAdd("d",-60,GETDATE())
Set @EndDate0 = GETDATE()
Set @EndDate61 = DateAdd("d",-61,GETDATE())
Set @StartDateOlder = DateAdd("d",-3650,GETDATE())
Set @EndDate = GETDATE()
Set @StartDate = DateAdd("d",-3650,GETDATE())
Set @EndDate81 = DateAdd("d",-81,GETDATE())
Set @StartDate80 = DateAdd("d",-80,GETDATE())

Set @COLLID = 'SMS00001'

select
            CS.Name0,CS.UserName0,CS.Domain0,CS.ResourceID,
      FCM.collectionID,
	  os.caption0 [OS],
CONVERT(VARCHAR(26), uss.lastscantime, 100) AS 'Last Su Scan',
IP.IPAddress AS [IP Address],

Case when sum(case when bl.clientstate >=1 then 1 else 0 end) > 0 then '*' end as 'Restart Required',

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 INT)))
else '0'
end as 'Patch Status',

case
when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDate30 and ui.DateRevised<@EndDate0 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDate30 and ui.DateRevised<@EndDate0 then 1 else 0 end)as INT)))
else '0'
end as '0 to 30',

case
when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDate60 and ui.DateRevised<@EndDate31 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDate60 and ui.DateRevised<@EndDate31 then 1 else 0 end)as INT)))
else '0'
end as '31 to 60',

case
when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDate80 and ui.DateRevised<@EndDate61 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDate80 and ui.DateRevised<@EndDate61 then 1 else 0 end)as INT)))
else '0'
end as '61 to 80',

case
when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDateOlder and ui.DateRevised<@EndDate81 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDateOlder and ui.DateRevised<@EndDate81 then 1 else 0 end)as INT)))
else '0'
end as '81 plus',

case
when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777243' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777243' then 1 else 0 end)as INT)))
else '0'
end as 'Critical',

case
when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777247' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777247' then 1 else 0 end)as INT)))
else '0'
end as 'Security',

case
when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777248' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777248' then 1 else 0 end)as INT)))
else '0'
end as 'Service Pack',

case
when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777250' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777250' then 1 else 0 end)as INT)))
else '0'
end as 'Rollup'

from
      v_UpdateComplianceStatus UCS
left outer join dbo.v_GS_COMPUTER_SYSTEM  CS on CS.ResourceID = UCS.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_gs_workstation_status ws on ws.resourceid=CS.resourceid
left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid

left join v_CombinedDeviceResources bl on bl.MachineID=ucs.resourceid

inner join v_UpdateInfo ui on ui.CI_ID=ucs.CI_ID
left join v_GS_OPERATING_SYSTEM OS on os.ResourceID=ucs.ResourceID
left join v_UpdateScanStatus USS on uss.ResourceID=ucs.ResourceID
left JOIN (SELECT	 IP1.resourceid AS rsid2, IPAddress = substring
				((SELECT	 (IP_Addresses0 + ', ')
				FROM	v_RA_System_IPAddresses IP2
				WHERE	 IP2.IP_Addresses0 NOT LIKE '169%' AND IP2.IP_Addresses0 NOT LIKE '0.%' AND IP2.IP_Addresses0 NOT LIKE '%::%' AND
				IP_Addresses0 NOT LIKE '192.%' AND IP1.resourceid = IP2.resourceid
				ORDER BY resourceid FOR xml path('')), 1, 50000)
				FROM	v_RA_System_IPAddresses IP1
				GROUP BY resourceid) IP ON IP.rsid2 = ucs.resourceid



Where FCM.collectionid = @COLLID
and ui.IsExpired=0 and ui.IsSuperseded=0

/*
######
Update Classifications
Modify and clause below to filter include only * Update Classifications
16777242	Applications
*16777243	Critical Updates
16777244	Definition Updates
16777245	Drivers
16777246	Feature Packs
*16777247	Security Updates
*16777248	Service Packs
16777249	Tools
*16777250	Update Rollups
16777251	Updates
16777252	WSUS Infrastructure Updates
16777466	Upgrades
######
*/

and (catinfo2.CategoryInstanceID = '16777243' 
or catinfo2.CategoryInstanceID = '16777247' 
or catinfo2.CategoryInstanceID = '16777248' 
or catinfo2.CategoryInstanceID = '16777250')

Group by CS.Name0,CS.UserName0,CS.Domain0,CS.ResourceID,FCM.collectionID,os.Caption0,uss.lastscantime,[IPAddress],OS.LastBootUpTime0
Order by CS.Name0,CS.UserName0,CS.Domain0,CS.ResourceID,FCM.collectionID,os.Caption0,uss.lastscantime,[IPAddress],OS.LastBootUpTime0

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

Declare @COLLID VARCHAR(50)

Declare @StartDate30 As Date

Declare @EndDate31 As Date

Declare @StartDate60 As Date

Declare @EndDate0 As Date

Declare @EndDate61 As Date

Declare @StartDateOlder As Date

Declare @EndDate As Date

Declare @StartDate As Date

Declare @EndDate81 As Date

Declare @StartDate80 As Date

Set @StartDate30 = DateAdd("d",-30,GETDATE())

Set @EndDate31 = DateAdd("d",-31,GETDATE())

Set @StartDate60 = DateAdd("d",-60,GETDATE())

Set @EndDate0 = GETDATE()

Set @EndDate61 = DateAdd("d",-61,GETDATE())

Set @StartDateOlder = DateAdd("d",-3650,GETDATE())

Set @EndDate = GETDATE()

Set @StartDate = DateAdd("d",-3650,GETDATE())

Set @EndDate81 = DateAdd("d",-81,GETDATE())

Set @StartDate80 = DateAdd("d",-80,GETDATE())

Set @COLLID = 'SMS00001'

select

CS.Name0,CS.UserName0,CS.Domain0,CS.ResourceID,

FCM.collectionID,

os.caption0 [OS],

CONVERT(VARCHAR(26), uss.lastscantime, 100) AS 'Last Su Scan',

IP.IPAddress AS [IP Address],

Case when sum(case when bl.clientstate >=1 then 1 else 0 end) > 0 then '*' end as 'Restart Required',

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 INT)))

else '0'

end as 'Patch Status',

case

when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDate30 and ui.DateRevised<@EndDate0 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDate30 and ui.DateRevised<@EndDate0 then 1 else 0 end)as INT)))

else '0'

end as '0 to 30',

case

when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDate60 and ui.DateRevised<@EndDate31 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDate60 and ui.DateRevised<@EndDate31 then 1 else 0 end)as INT)))

else '0'

end as '31 to 60',

case

when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDate80 and ui.DateRevised<@EndDate61 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDate80 and ui.DateRevised<@EndDate61 then 1 else 0 end)as INT)))

else '0'

end as '61 to 80',

case

when (sum(case when UCS.status=2 and ui.DateRevised>=@StartDateOlder and ui.DateRevised<@EndDate81 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and ui.DateRevised>=@StartDateOlder and ui.DateRevised<@EndDate81 then 1 else 0 end)as INT)))

else '0'

end as '81 plus',

case

when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777243' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777243' then 1 else 0 end)as INT)))

else '0'

end as 'Critical',

case

when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777247' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777247' then 1 else 0 end)as INT)))

else '0'

end as 'Security',

case

when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777248' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777248' then 1 else 0 end)as INT)))

else '0'

end as 'Service Pack',

case

when (sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777250' then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 and catinfo2.CategoryInstanceID = '16777250' then 1 else 0 end)as INT)))

else '0'

end as 'Rollup'

from

v_UpdateComplianceStatus UCS

left outer join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = UCS.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_gs_workstation_status ws on ws.resourceid=CS.resourceid

left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid

left join v_CombinedDeviceResources bl on bl.MachineID=ucs.resourceid

inner join v_UpdateInfo ui on ui.CI_ID=ucs.CI_ID

left join v_GS_OPERATING_SYSTEM OS on os.ResourceID=ucs.ResourceID

left join v_UpdateScanStatus USS on uss.ResourceID=ucs.ResourceID

left JOIN (SELECT IP1.resourceid AS rsid2, IPAddress = substring

((SELECT (IP_Addresses0 + ', ')

FROM v_RA_System_IPAddresses IP2

WHERE IP2.IP_Addresses0 NOT LIKE '169%' AND IP2.IP_Addresses0 NOT LIKE '0.%' AND IP2.IP_Addresses0 NOT LIKE '%::%' AND

IP_Addresses0 NOT LIKE '192.%' AND IP1.resourceid = IP2.resourceid

ORDER BY resourceid FOR xml path('')), 1, 50000)

FROM v_RA_System_IPAddresses IP1

GROUP BY resourceid) IP ON IP.rsid2 = ucs.resourceid

Where FCM.collectionid = @COLLID

and ui.IsExpired=0 and ui.IsSuperseded=0

######

Update Classifications

Modify and clause below to filter include only * Update Classifications

16777242 Applications

*16777243 Critical Updates

16777244 Definition Updates

16777245 Drivers

16777246 Feature Packs

*16777247 Security Updates

*16777248 Service Packs

16777249 Tools

*16777250 Update Rollups

16777251 Updates

16777252 WSUS Infrastructure Updates

16777466 Upgrades

######

and (catinfo2.CategoryInstanceID = '16777243'

or catinfo2.CategoryInstanceID = '16777247'

or catinfo2.CategoryInstanceID = '16777248'

or catinfo2.CategoryInstanceID = '16777250')

Group by CS.Name0,CS.UserName0,CS.Domain0,CS.ResourceID,FCM.collectionID,os.Caption0,uss.lastscantime,[IPAddress],OS.LastBootUpTime0

Order by CS.Name0,CS.UserName0,CS.Domain0,CS.ResourceID,FCM.collectionID,os.Caption0,uss.lastscantime,[IPAddress],OS.LastBootUpTime0

The RDL for SSRS is below for you your convenience. Remember to update the DataSource and modify the @COLLID Parameter to modify the Collections available to the report.

Download Here
Compliance 12 – Patches Required for Collection_121120_Public.rdl

*UPDATE* 12/11/2020 – RDL updated to remove invalid link to custom Drill through report.
Please remember to select your own data source after uploading the RDL to your Report Server
You will also need to edit the RDL with a text editor and replace ConfigMgr_SS1 with ConfigMgr_XXX where XXX is your Site Code

Had help from a few folks on this along the way:-

Eswar has great examples of SSRS reports on his blog http://eskonr.com/
Adam Gross is just a selfless SCCM Ninja who I respect greatly. When i just about gave up with Case SQL Statements he jumped in and helped out https://www.asquaredozen.com/
Garth Jones for writing an invaluable book on SSRS and lending an Olive Branch http://www.enhansoft.com/blog/author/garth/page/13
Chris Buck for being super nice and offering help https://sccmf12twice.com

4.5/5 - (2 votes)

36 thoughts on “SCCM Software Compliance Report Filtered by Update Classification and Update Age”

Marcel Moerings
December 17, 2018 at 7:32 am

Which database rights does the account that runs the script need on the database? When running the script it gives me this error

The SELECT permission was denied on the object ‘BGB_LiveData’, database ‘ConfigurationManager_P01’, schema ‘dbo’.
—————————-
Query execution failed for dataset ‘DataSet1’. (rsErrorExecutingCommand)
—————————-
An error has occurred during report processing. (rsProcessingAborted)

Reply
1. Ben Whitmore
  December 17, 2018 at 8:47 am
  
  Hi Marcel, I have update the RDL now so the Select statement runs against a view instead of the live SQL table. Try downloading it again or update that part of the query: Replace “left join BGB_LiveData bl on bl.resourceid=ucs.resourceid” with “left join v_CombinedDeviceResources bl on bl.MachineID=ucs.resourceid”
  
  Reply
Marcel Moerings
December 17, 2018 at 9:06 am

Works like a charm now. Thanks!

Reply
1. Ben Whitmore
  December 17, 2018 at 9:07 am
  
  Great 🙂 ps. You may need to modify the deep link to the detailed reports too (when you click a device name)
  
  Reply
  1. Anonymous
    December 17, 2018 at 9:13 am
    
    I noticed… 🙂
    
    The item ‘/ConfigMgr_PO1/Software Updates – A Compliance/Compliance 5a – Specific computer (With Update Revised Date)’ cannot be found. (rsItemNotFound)
    
    Reply
    1. Ben Whitmore
      December 17, 2018 at 10:08 am
      
      Choose the built in report Compliance Report 5
      
      Reply
      1. Marcel Moerings
        December 17, 2018 at 10:28 am
        
        In our environment your report tells 8 updates are required and it tells what classifications, the linked report however (number 5) gives a whole load of patches and we have to manually find out what the 8 required patches are. Would it be possible to adjust or create a report so only the 8 required show in the report?
      2. Ben Whitmore
        December 17, 2018 at 10:41 am
        
        You can copy and then modify the inbuilt report “Compliance 5” and add this to the “Where” clause on line 60
        
        and (cls.CategoryInstanceName='Critical Updates' or cls.CategoryInstanceName = 'Security Updates' or cls.CategoryInstanceName = 'Service Packs' or cls.CategoryInstanceName = 'Update Rollups')
Ben Weaver
December 24, 2018 at 4:25 pm

Hello fellow Ben.

Thanks for this report! It built on a report I was using and made it much more useful.

Something that helps me with assigning collections to RDL’s is setting up a query for the Parameter @COLLID. In that query I look for specific text in the Comment field of all of my collections, so that I can add or remove collections without editing my reports. Below is the query for @COLLID. Set the Value Field for SiteID and the Label Field for CollectionName.

Select distinct col.CollectionName
,col.SiteID
From v_FullCollectionMembership fcm
JOIN Collections col on col.SiteID = fcm.CollectionID
WHERE CollectionComment LIKE ‘%_Compliance_rpt%’
Order By col.CollectionName

Reply
Daryl
May 11, 2020 at 4:48 am

If I wanted to exclude “Patch my PC” as a vendor from the totals, how would I modify this query to work?

Reply
Mike
September 14, 2020 at 2:39 pm

I know this is an older post, but i am just starting with updates and think this can help. When i am selecting a host name i am getting an error: The item ‘/ConfigMgr_KC1/Software Updates – A Compliance/Compliance 5a – Specific computer (With Update Revised Date)’ cannot be found. (rsItemNotFound)
I see you have responded to someone with “Choose the built in report Compliance Report 5” but i dont know where i need to make that change.
Thank you.

Reply
1. Ben Whitmore
  September 22, 2020 at 4:22 pm
  
  Hi Mike,
  
  You need to update the Data Source using Report Builder to point to your own site database. See Eswar’s blog here http://eskonr.com/2014/04/sccm-configmgr-2012-how-to-change-custom-data-source-to-shared-data-source-for-multiple-ssrs-reports/
  
  Reply
  1. Ben Whitmore
    September 29, 2020 at 2:11 pm
    
    Hi Mike,
    
    Sorry, I see the issue now. As well as changing the DataSource to point to your CM Database, the “Drill through” link on the “Server name” also needs changing. The embedded link points to another custom report I have. Try the following:-
    
    1. Open RDL in Report Builder
    2. Right Click the Server <> Field and choose “Placeholder Properties”
    3. Choose Action > Go to report and click “Browse”
    5. Choose “Compliance 5 – Specific computer”
    6. Choose “Open”
    7. Press “OK”
    
    Reply
Khaled
September 29, 2020 at 2:33 am

Hi Ben,
First of all I wanted to thank you for sharing this report.
This report works fine, however I have the same error as Mike (The item ‘/ConfigMgr_KC1/Software Updates – A Compliance/Compliance 5a – Specific computer (With Update Revised Date)’ cannot be found. (rsItemNotFound) when I click on a computer name for further patch details.
I already changed the data source using report builder to point to my own site database to make the report work. I looked at Eswar’s blog and I have already done those steps. Any help will be much appreciate.

Reply
1. Ben Whitmore
  September 29, 2020 at 2:11 pm
  
  Hi Jari,
  
  Sorry, I see the issue now. As well as changing the DataSource to point to your CM Database, the “Drill through” link on the “Server name” also needs changing. The embedded link points to another custom report I have. Try the following:-
  
  1. Open RDL in Report Builder
  2. Right Click the Server <> Field and choose “Placeholder Properties”
  3. Choose Action > Go to report and click “Browse”
  5. Choose “Compliance 5 – Specific computer”
  6. Choose “Open”
  7. Press “OK”
  
  Reply
  1. Khaled
    September 29, 2020 at 10:22 pm
    
    I just followed your instructions and saved it. When I run the saved report and click on a server name, the same error occurs.
    
    Reply
Shayan
October 1, 2020 at 10:29 am

Hi Khaled,

You need to also edit the text box properties around the expression with the same action go to url to the location of compliance 5 report.
Thanks Ben!! works brilliantly!

Reply
1. Ben Whitmore
  October 1, 2020 at 10:31 am
  
  Thank you for spotting that Shayan 🙂
  And thank you for the feedback 🙂
  
  Reply
2. Khaled
  October 1, 2020 at 5:27 pm
  
  This is awesome, Thank You Shayan, it now works!
  Thanks again Ben!
  
  Reply
kacey
October 30, 2020 at 4:10 am

Hi Ben,

I try update the sql with other device collection, this collection have 1000 client. When I run the report, it just show only 7xx client. Can you guy me which sql statement have limit\filter the client

Thanks

Reply
Kacey
October 30, 2020 at 4:31 am

Hi Ben,

I have update the collection to others which have ~1000 client. When I run the report, it just only show 7xx client. Can you guide me which sql statement had limit\filter the client.

Thanks

Reply
1. Ben Whitmore
  October 31, 2020 at 11:24 am
  
  Are you running the report in Configuration manager or Report Builder and seeing this limit Kacey?
  
  Reply
  1. kacey
    November 3, 2020 at 4:48 am
    
    Hi Ben,
    
    I run on the configuration manager
    
    BR
    
    Reply
    1. Ben Whitmore
      November 8, 2020 at 8:34 am
      
      Hey, I am not aware of a limit in this report.
      
      Reply
Kenny
November 11, 2020 at 11:56 pm

I have tried all the steps and still get this when I click on a computer name for further patch details. (The item ‘/ConfigMgr_KC1/Software Updates – A Compliance/Compliance 5a – Specific computer (With Update Revised Date)’ cannot be found. (rsItemNotFound). Can you please assist, step by step with photos if possible

Reply
1. Ben Whitmore
  November 12, 2020 at 3:00 am
  
  As Shayan posted
  
  “ You need to also edit the text box properties around the expression with the same action go to url to the location of compliance 5 report.”
  
  I’ll re-upload the RDL later without the link to the custom report as it can be confusing
  
  Reply
  1. Kenny
    November 12, 2020 at 1:13 pm
    
    That will be appreciated. I did as you said but still errored out as above. I must be missing something. Maybe the old report as is and it can point me to where the /ConfigMgr_KC1/Software Updates – A Compliance/Compliance 5a – Specific computer is.
    Thanks
    
    Reply
    1. Ben Whitmore
      November 12, 2020 at 1:20 pm
      
      Uploading modified RDL now – You need to open the RDL with a text editor and replace ConfigMgr_SS1 with ConfigMgr_XXX where XXX is your Site Code. Then upload the RDL to your reports server.
      
      Reply
Kenny
November 12, 2020 at 3:06 pm

Thanks you so much Ben! its working now. In addition to the site code change I had to ensure the path is Software Updates – A Compliance/Compliance 5a – Specific computer, removing the other stuff after.
You are Chingon! as the say in spanish

Reply
1. Ben Whitmore
  November 12, 2020 at 4:16 pm
  
  Thanks Kenny, I have updated the RDL again – I missed the other “Compliance 5a” reference – sorry :/
  
  Reply
sk4ever
November 19, 2020 at 11:49 am

How can we add or prompt collection name to while running the report? I don’t want to edit the report every time for different collections.

Reply
Sergio Alves
November 30, 2020 at 7:10 pm

Hello,
Thanks for the great report!
I noticed that some of my WIndows Serevr 2016 (v1607) and some Windows 10 are getting the same results:
0 to 30: 11
31 to 60: 4
61 to 80: 9
81 plus: 60
But the clients are updated till October 2020.
Is there any issue with these clients?
Thanks in advanced!

Reply
1. Ben Whitmore
  November 30, 2020 at 7:15 pm
  
  Hi Sergio, the click through report will indicate which updates are required by your clients. It might be that some updates have not been “approved” but are “required” by your clients.
  
  Reply
  1. Anonymous
    November 30, 2020 at 10:08 pm
    
    Just found the issue. Nothing to do with your Report.
    I Have some VDI Servers with Office installed and SCCM was not deploying the Updates to them (the query on the Collection was only for clients). So, the results from the Report were from Office Updates.
    Thanks!
    
    Reply
Anonymous
November 30, 2020 at 10:08 pm

Just found the issue. Nothing to do with your Report.
I Have some VDI Servers with Office installed and SCCM was not deploying the Updates to them (the query on the Collection was only for clients). So, the results from the Report were from Office Updates.
Thanks!

Reply
Sergio Alves
November 30, 2020 at 10:09 pm

Just found the issue. Nothing to do with your Report.
I Have some VDI Servers with Office installed and SCCM was not deploying the Updates to them (the query on the Collection was only for clients). So, the results from the Report were from Office Updates.
Thanks!

Reply

36 thoughts on “SCCM Software Compliance Report Filtered by Update Classification and Update Age”

Leave a Comment Cancel Reply