Quickly Find Missing Files with SCCM CMPivot Query

In this post I will show you how to find missing files with SCCM CMPivot query. We will run the CMPivot query that returns computers that are missing a specific file from a specific path.

In some cases you many need to find missing files from your devices. The task would seem next to impossible when you have lot of computers in the list. You cannot login to every computer and find missing files. So in such situations you can use the SCCM CMPivot to find missing files.

For example, you have deployed a script and you want to ensure the the necessary files are present on every device in your setup. Or it could be the case where you want to check if a specific file is present in a folder on remote computers. There are alternate methods to find missing files from every device however you can find missing files with SCCM CMPivot query easily. You can run a remote query on the devices and get the real time data.

First of all, you must enable or turn on the community hub feature in Configuration Manager. It’s pretty easy and simple task. Go to Administration\Overview\Updates and Servicing\Features and right click Community Hub feature and click Turn on. If you encounter community hub loading issues, here is a post for you to fix SCCM community hub issues.

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.

Note – We will run the built-in SCCM CMPivot query to find missing files on a device collection that consists of Windows 10 devices. 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.

Find Missing Files with SCCM CMPivot Query

So let’s find missing files with SCCM CMPivot query on Windows 10 devices.

• Launch the Configuration Manager console.
• Navigate to Assets and Compliance > Overview > Device Collections.
• Right click the device collection and click Start CMPivot.

In the CMPivot tool, select the Query tab and in the right corner click Community Hub queries. Wait for few seconds while the tool connects to Community Hub and loads the CMPivot queries. From the list select Find devices with missing files and double click the query.

The CMPivot query (find missing files) now loads in the query editor. If it doesn’t then use the below SCCM CMPivot query to find missing files from Windows devices.

Device
| join kind=leftouter (File('C:\Program Files\7-Zip\7z.exe'))
| where isnull(FileName)
| project Device

In the above query, you need to specify the file path correctly. For example, the above SCCM CMpivot query finds the missing files from the system (7z.exe) and you must provide a valid file path. The query returns the computers that do not have the file present.

Out of 4 computers, the 7z.exe is present on 1 computer and rest of the 3 computers are shown in the query output.

Another test and this time we will check if CMTrace tool is missing on our devices. The CMTrace tool is located in C:\Windows\CCM\ folder. We will run the same find missing files CMPivot query on same device collection. Now surprisingly I got one computer that doesn’t have the CMTrace tool and when I manually checked the location, the file was missing.

So in conclusion it is very easy to find missing files with SCCM CMPivot query and the query works perfectly fine. If you have any questions, feel free to leave them in the comments section.