You can use SCCM CMPivot Query to find WSUS Server details and troubleshoot software updates deployments. The ConfigMgr CMPivot query can be used to find WSUS Server details of the clients such as Catalog version, SUP Fall backup option details, and port number.
CMPivot Queries in Configuration Manager provides access to real-time state of devices in your environment. For example, you can quickly find all the WSUS server details using CMPivot query in SCCM. You can launch the CMPivot tool and run a query on all currently connected devices in the target collection and get the results.
The good news is you can deploy CMPivot standalone app using SCCM (ConfigMgr) on computers. With the standalone CMPivot app, admins don’t need to log in to SCCM server every time to run CMPivot queries.
Read: How to find Dot NET version details using CMPivot query
The CMPivot query to find WSUS server details is easy, and the output can be exported to a file. This makes the CMPivot a useful tool to run queries and fetch information in quick time. You can read more about CMPivot Queries from Microsoft documentation.
Launch the CMPivot Tool from ConfigMgr Console
Launch the SCCM CMPivot tool to find WSUS server details:
- Click Start and launch the SCCM console.
- Navigate to Assets and Compliance > Overview > Device Collections.
- Select a device collection where want to run the CMPivot query and click Start CMPivot.
CMPivot Query to Find WSUS Server Connected to Clients
You may have multiple WSUS servers running in your SCCM setup and to find the WSUS server connected to clients, you can use CMPivot query.
Note: There are other methods to find WSUS Server details on client computers which include registry, group policy etc. However, if you are using SCCM, I recommend using CMPivot query to find WSUS server details as it is simple and efficient.
Read: How to Check Registry Values using SCCM CMPivot Query
When you have multiple WSUS servers, you want to know what WSUS server is my client contacting. The best to get connected WSUS server information is by reviewing the LocationServices.log. The LocationServices.log file is located on the client computer that records the client activity for locating management points, software update points, and distribution points.
You can install more than one software update points on a site. The first software update point that you install is configured as the synchronization source, which synchronizes the updates from Microsoft Update or from the upstream synchronization source.
Run the following CMPivot query to find the WSUS Server associated with the client computer. The below CMPivot query runs on every client computer and checks the LocationServices.log for WSUS Server information.
CcmLog('LocationServices') | where Logtext contains 'WSUS'
The WSUS Path = ‘http://wsusserverfqdn:8530‘ is the actual WSUS server name associated with the remote SCCM client computer. If you have multiple computers contacting different WSUS servers, the CMPivot query can also find that out.
Now that we know the WSUS Server connected to the SCCM clients, we can use the same CMPivot query and filter other WSUS Server details.
Run the following CMPivot query to get the WSUS Path, Server name, Version and SUP fallback details from remote SCCM client computers.
CcmLog('LocationServices') | where LogText contains 'WSUS Path=' | project Device, LogText, DateTime
CMPivot Query to Find Clients Connected to Specific WSUS Server
Let’s find out how many of your SCCM client computers are connected to a specific WSUS server in your setup using a CMPivot query. In the CMPivot query window, run the following command:
CcmLog('LocationServices') | where LogText contains 'http://corpcm.mecmlab.local:8530' | project Device, LogText, DateTime
The above CMPivot query reads the LocationServices.log entries from the client computers and looks at the log text that contains the WSUS server name and outputs the details. This CMPivot query finds the WSUS Server details from online clients.
There is an export option in CMPivot query window where you can export the WSUS Server details of client computers to an external file.
