Recently ,we had an issue with SCCM Configmgr Reporting services role (Remote SQL sitting on VM was crashed ,blog post coming soon ) and we were unable to generate reports mainly for the Software update compliance status that happens every month. This post is about ,how to check the software update compliance status for the deployed software update group/Groups per collection/collections without using Configmgr Reports . The reason for posting this blog is ,fixing the Configmgr Reporting services role took more than expected time and meantime ,we need to check the status of patch compliance status and troubleshoot the non-compliant machines (servers) within the Maintenance window.
I thought the SQL Code which I used to generate the compliance status would be handy for others if they do not want to Use configmgr Reports and use SQL Code for Quick results.
If you are unable to generate compliance status using the SSRS Reports ,the only possible method is ,to depend on Monitoring node—deployments ,look for the software update deployment for particular collection and see the non-compliant machines for troubleshooting which is not easy method if you have large number of deployments and collections.
So ,to overcome this ,you can USE SQL management studio and run the query (posted below) to generate non-compliance list of clients with extra information like hardware inventory,software update scan,,Operating System ,IP address,User Name ,does it have Client etc.
This SQL Query should be helpful to quickly generate compliance Status for multiple software update groups and for multiple collections.
I recently posted a blog about ,how to generate software update compliance Status for multiple update groups per collection but this SQL query helps to generate update compliance status for multiple updates groups against multiple collections.
you can use this SQL query to create nice SSRS Report for multiple update groups per multiple collections in OneClick.
The below SQL code is for list of clients with required/Missing Status ,If you want clients with Unknown ,change the @status value to 0 ,More about Update compliance Status ,see below :
Update compliance Status:
0—Detection Status Unknown
1—Not Applicable
2—Required/Missing
3—Already Installed /Compliant
--SQL Code to Generate Update compliance Status for multiple update groups against multiple collections
Declare @Status nvarchar(255);set @Status='2';
--Status 0 for Unknown, 1 for Not Applicable,2 for Required ,3 for installed
select sys.name0 [Computer Name],sys.User_Name0 [User Name], os.caption0 [OS],
CONVERT(VARCHAR(26), ws.lasthwscan, 100) as [LastHWScan],
CONVERT(VARCHAR(26), uss.lastscantime, 100) AS 'LastSUScanTime',
CONVERT(VARCHAR(26), sys.last_logon_timestamp0, 100) AS 'Last Logon Time',
case when sys.client0='1' then 'Yes' else 'No'
end as 'Client (Yes/No)', c.IPAddress AS [IP Address]
From v_Update_ComplianceStatusAll UCS
left join v_r_system sys on ucs.resourceid=sys.resourceid
left join v_FullCollectionMembership fcm on sys.resourceid=fcm.resourceid
left join v_collection coll on coll.collectionid=fcm.collectionid
left join v_GS_OPERATING_SYSTEM os on ucs.resourceid=os.resourceid
left join v_gs_workstation_status ws on ucs.resourceid=ws.resourceid
left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
left join v_AuthListInfo LI on ucs.ci_id=li.ci_id
INNER 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) c ON c.rsid2 = ucs.resourceid
where li.title IN (‘SUG1’,'SUG2’,SUG3’) and coll.collectionID in ('PS10029A','PS10000D')and ucs.status=@Status
group by sys.name0,sys.User_Name0,os.Caption0,ws.LastHWScan ,uss.LastScanTime,sys.Last_Logon_Timestamp0,sys.client0,c.IPAddress
order by 1
Hope it helps !
12 Comments
Pingback: SCCM ConfigMgr Compliance status of client for multiple software update groups | Eswar Koneti Blog
Awesome man! been trying to find this for a while. I now need to convert it into a WQL Query, so I can call it from Powershell
cool .Why do you need WQL ? are you going to create collection for this ? Reports are far better than collections for monitoring and you can do subscription based as well to get reports via email.
Thanks,
Eswar
I use to deploy SUG to 2 different collections (One for Workstation and another for Server), My business requirement is to Generate a report a single Overall compliance with two tables Server and Workstations.
Please help me
you can create one collection and use include option to add both the collection into this and run the report to get overall compliance for specific update group.
Regards,
Eswar
Pingback: Patching by Orchestrator Part -2 – Tadgata
Hi Eshwar,
that query is very good to track the the machines that need required updates as per Software Update group. i just run such query and found 3 machines needs required updates but i also want to know the Updates KB or required updates details so that i can look into deeper.. is this feasible to modify such query accordingly...?
have you looked at this post ? it does have linked reports to show you the list of clients with missing patches etc. http://eskonr.com/2016/05/sccm-configmgr-software-update-compliance-report-for-multiple-software-update-groups-per-collection/
Regards,
Eswar
Hello Eswar,
How you manage the assets with an outdated WUA agent? Because it will show up into the "Installed/Not Applicable" column which is wrong. You know that for example for Win2012R2 if you have a WUA version less than 7.9.9600.16422 the server will not receive any updates and it will pop up as compliant.
Thanks,
Adrian
Hi Adrian,
based on the link https://support.microsoft.com/en-sg/help/949104/how-to-update-the-windows-update-agent-to-the-latest-version ,you need to upgrade your old version of windows update agent versions to latest on applicable OS as per the technet.
I will write blog post on how to use compliance settings to detect older version of windows update agent which will help you to update the WUA agent.
Regards,
Eswar
That's not a problem for me, I already build a TS to deploy all 7 KBs for 2012 and a package for 2008. But my environment has more than 20 000 assets and is difficult to manage the mw for all of them. I also build a report, reporting the wua.version0 for all assets.
I just want to see your opinion on how to integrate and report those outdated wua in this compliance report.
My suggestion is to add into the ucs status 2 (the Required column) the old wua versions and the "good" wua version into the "Installed" one.
Maybe you think at something else.
ya ,thats good idea but it is always good to have that as separate collection to identify if any old WUA and bring to current version.
I never focus on WUA into the report because it is always require to have latest WUA to get the patching done .
Regards,
Eswar