CMPivot Queries

Since SCCM 1806, Microsoft have included a great tool called CMPivot to perform live queries across all currently connected SCCM clients, excluding those which are offline.

Based on the Kusto query language that is also used for Azure Log Analytics, you can build queries to search or report on something specific. Even a simple query may take some time to complete, including the number of clients in the collection and the time needed for the query to fetch the results.

There are many queries that you can define and then export the results. To access CMPivot:

  1. Open Configuration Manager Console
  2. Navigate to Assets and Compliance > Device Collections > choose a desired collection
  3. In the [Home] tab ribbon under 'Collection’, select [Start CMPivot]
  4. In the [Query] tab, locate the desired item under 'Entities' 

Search for all computers on specific BIOS version: 


Bios | where (Name == ‘231.1737.770’) 

Search for a user’s computer: 


Device | where (UserName like ‘DOMAIN\MyUser’) 

Report all computers that do not have BitLocker enabled: 


BitLocker | where ProtectionStatus == 0