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.

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

Launching CMPivot Query Tool

  • Navigate to device collection against that you want to run the CMPivot query.
  • Select All Systems or any collection that you want to run the CMPivot.

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.
Launch SCCM CMPivot Query Tool - ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM
ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM

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.

Patch My PC

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 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.

1E Nomad

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

CcmLog('LocationServices') | where LogText contains (('WSUS Path=') and ('http://CMMEMCM.MEMCM.COM:8530')) 
| project Device, LogText, DateTime
ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM
ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM

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
ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM
ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM
  • 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
ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM
ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM

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.

DeviceLogTextDateTime
Prod-Win20WSUS Path=’http://CMMEMCM.MEMCM.COM:8530‘, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′03-08-2021 12:22
Prod-Win20WSUS Path=’http://CMMEMCM.MEMCM.COM:8530′, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′03-08-2021 12:22
Prod-Win20WSUS Path=’http://CMMEMCM.MEMCM.COM:8530′, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′03-08-2021 12:22
Prod-Win20WSUS Path=’http://CMMEMCM.MEMCM.COM:8530′, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′03-08-2021 12:22
Prod-Win20WSUS Path=’http://CMMEMCM.MEMCM.COM:8530′, Server=’CMMEMCM.MEMCM.COM’, Version=’27’, LocalityEx=’BOUNDARYGROUP’, SUPFallbackIn=’0′03-08-2021 12:22
ConfigMgr CMPivot Query to Fetch WSUS Server Details of the Clients | SCCM

Resources

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.