Using the SCCM CMPivot query you can find installed service on Windows devices. In this post we will query or find installed service with CMPivot query.
At times you may be asked to find out or query a specific service that is installed on your devices. It could be SCCM service (ccmexec) or antivirus service etc. More interesting, you may also need to find out if the service is running on a remote machine or it’s in a stopped state. You cannot remote in every device and check the installed service because it is not practically possible when you have huge number of computers.
One alternate method can be to open the services console on the computer and connect to the remote machine. But again if you have got several computers in your list, this is practically not possible and a time consuming task.
CMPivot is a new in-console utility that now provides access to real-time state of devices in your environment. It immediately runs a query on all currently connected devices in the target collection and returns the results. If you have the CMPivot queries with you, your task is going to be really simple.
We will run the CMPivot query on a device collection that consists of Windows 10 devices and find the installed service on each device. The devices have to be online in order to retrieve the installed service on them. In case the device is offline or not contactable, you would get to know about it in the query output.
To find installed service on windows device, we are going to use a query that is included in community hub. The name of this query is List of devices which have a service installed. You can use this query and tweak it further as per your requirements.
Find Installed Service with CMPivot Query
So let’s find installed service with CMPivot query (SCCM cmpivot) on windows devices
- Launch the Configuration Manager console.
- Navigate to Assets and Compliance > Overview > Device Collections.
- Right click Windows 10 device collection and click Start CMPivot.
In the Query tab, paste the below query. Ensure you replace the service name with the one that you want to find. For example the below CMPivot query finds the CCMExec service installed on all machines in the device collection.
Service | where (Name == 'CCMExec') | summarize count() by Device
Enter the query and click Run Query. In few seconds the query should list all the computers installed with CCMExec service in the query output.
So we have all the four devices that have CCMExec installed. The query that we are using works fine.
Here is the interesting part. When you select any device the CMPivot query changes and limits to that single device.
| where (Name == 'CCMExec')
| where (Device == 'CORPENT01')
Here you can find the state of the service as well. The output shows the device name, service name and state of the service. It could be either Stopped or Running. You also get to see the StartMode and Status of the service.
Find Service State using CMPivot Query
Ok so now that you can find installed service with SCCM CMPivot, let’s use another query to find the service state on these devices. The service state determines whether a service is running or in a stopped state. This query is useful when you want to find out if a critical service is running on devices or not.
The prerequisite for this command is you need the service name. The service name can be retrieved from the service properties. Ensure you are using the correct service name in the below query.
In the CMPivot Query tab, enter the below query to find the service state.
Service | where (Name == 'CCMExec'and State == 'Running')
The output shows the devices and the service state. The CCMExec service is installed and running on all the devices.