ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM | Configuration Manager | Endpoint

Let’s look at a ConfigMgr CMPivot query to Fetch WSUS Server Details of the Clients that might help you with software update troubleshooting.

I have many blog posts related to CMPivot queries, and some of them are listed below. All of these posts explain how to start running CMPivot queries against Online clients.

• Out of Support Office 365 ProPlus
• Patches Installed in the Last 90 Days
• Windows 10 English Language Devices
• Find Devices Missing Patches
• Devices with Greater than 15 GB Free Disk Space

NOTE! –  CMPivot uses a subset of the Kusto Query Language (KQL). More details about KQL from Microsoft Docs.

Launching CMPivot Query Tool

Let’s understand how to launch the CMPivot query tool.

• Navigate to the device collection against which you want to run the CMPivot query.
• Select All Systems or any collection that you want to run the CMPivot.
  • Or you can use CMPivot standalone tool.

NOTE! – In a production environment with more than many devices, I don’t recommend using All System collection. Instead, use the collection with a smaller number of devices to try out the query.

• Right-Click on the All Systems collection.
• Select Start CMPivot.

Issues with CMPivot Query & Troubleshooting

I initially tried to get machines connected to a particular WSUS server using the CMPivot entity called CCMLog. However, the query didn’t work because of formatting issues.

Unfortunately, I couldn’t find a working CMPivot query. If you know how to use nested quotes (‘ ‘ ‘ ‘) in CMPivot, please let me know in the comments section.

The following query in KQL format didn’t work for me because the contains operator doesn’t support double quotes ‘WSUS Path=’http://CMMEMCM.MEMCM.COM:8530”

CMPivot Query Error – Failed to parse the query.

CcmLog('LocationServices') | where LogText contains 'WSUS Path='http://CMMEMCM.MEMCM.COM:8530'' 
| project Device, LogText, DateTime

I try to split the contains operator text into two parts as shown in the below query and that also didn’t work.

CMPivot Query Error – Left and right sides of the logical expression must be Boolean.

CcmLog('LocationServices') | where LogText contains (('WSUS Path=') and ('http://CMMEMCM.MEMCM.COM:8530')) 
| project Device, LogText, DateTime

CMPivot Query to Fetch CMPivot Details

Let’s check the query that we are going to use to CMPivot Query to Fetch WSUS Server Details from all online clients. Also, there is another CMPivot query that can look for the clients connected to one particular WSUS/SUP server.

• Let’s check the query to find out the clients connected to one particular WSUS/SUP server.
  • Make sure to update the following query with your WSUS server and port number that is in use.

CcmLog('LocationServices') | where LogText contains 'http://CMMEMCM.MEMCM.COM:8530'
| project Device, LogText, DateTime

Let’s check the query to find out the list of all ONLINE clients and their WSUS server details.

CcmLog('LocationServices') | where LogText contains 'WSUS Path=' 
| project Device, LogText, DateTime

Results

You can check the WSUS server details, WSUS Catalog version, SUP Fall backup option details, and port number with the above-mentioned ConfigMgr CMPivot related query.

So if you want to perform software update troubleshooting on problematic clients, you can use the CMPivot entity called CCMLog.

Device	LogText	DateTime
Prod-Win20	WSUS Path=’http://CMMEMCM.MEMCM.COM:8530‘, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′	03-08-2021 12:22
Prod-Win20	WSUS Path=’http://CMMEMCM.MEMCM.COM:8530′, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′	03-08-2021 12:22
Prod-Win20	WSUS Path=’http://CMMEMCM.MEMCM.COM:8530′, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′	03-08-2021 12:22
Prod-Win20	WSUS Path=’http://CMMEMCM.MEMCM.COM:8530′, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′	03-08-2021 12:22
Prod-Win20	WSUS Path=’http://CMMEMCM.MEMCM.COM:8530′, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′	03-08-2021 12:22

Resources

• ConfigMgr CMPivot Architecture – https://www.anoopcnair.com/sccm-cmpivot-guide/

Author

Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.