CMPivot Query to Find Windows 10 Devices from SCCM

Let’s try to use the CMPivot query to find Windows 10 devices from SCCM. There ate different ways to find Windows 10 devices from SCCM. CMPivot is one of the easiest and quickest ways to find the latest information about Windows 10 devices.

I have already shared tips about CMPivot Query to find Windows 10 English Language Devices. The CMPivot query to find Windows 10 devices quickly is useful to segregate Windows 11 and Windows 10 devices. I also share the different methods to find Windows 10 devices in this post.

If you are looking for a WQL query to create a collection to find Windows 10 devices, then you can head over to the best practices blog post. You can also use the same CMPivot queries from the Microsoft Endpoint Manager portal to collect the same details about co-managed/tenant attached devices.

CMPivot Query using Window 10 Build Number

You can use the following query to find Windows 10 devices. This query is using the same logic that I used in the above-mentioned blog post. The main logic behind this query is the maximum and minimum build numbers of Windows 10.

Patch My PC
  • Maximum build number for Windows 10 will less than 20000 for sure(?yes).
  • Minimum build number for Windows 10 is greater than 10000.

You can launch the CMPivot tool from the console or as a standalone tool. Select any device collection, and Run CMPivot button from the ribbon menu to launch CMPiovt. You can click on the query tab and paste the following query.

OperatingSystem | where BuildNumber < '20000' and BuildNumber > '10000'

NOTE! – You can see the results in the below screenshot. One is highlighted in bold, and the other record is with normal color. Well, it seems CMPivot can now show the basics details of OFFLINE PCs as well. PROD-WIN21 PC is offline for many days but still shows up in Windows 10 CMPivot query.

CMPivot Query to Find Windows 10 Devices from SCCM
CMPivot Query to Find Windows 10 Devices from SCCM

CMPivot Query based on Caption

Let’s quickly check the way to find out Windows 10 devices using CMPivot query based on Caption of the operating system. This query will collect the devices with Windows 10 Enterprise version. If you manage devices with some other versions of Windows 10 like education, you will need to change this query to Microsoft Windows 10 Education.

1E Nomad
OperatingSystem | where Caption == 'Microsoft Windows 10 Enterprise'
OperatingSystem | where Caption == 'Microsoft Windows 10 Eduation'
CMPivot Query to Find Windows 10 Devices from SCCM
CMPivot Query to Find Windows 10 Devices from SCCM

Graphical Represenation using CMPivot query

You can create some graphical representation of the Windows 10 data using the CMPivot tool and query. Some of the other default options to render graphs are not supported for the Count () function. The following examples help you produce the total count of Windows versions within SCCM, and it will be represented as a bar chart, column chart, or pie chart.

OperatingSystem | summarize count() by Caption | render barchart
OperatingSystem | summarize count() by Caption | render columnchart
OperatingSystem | summarize count() by Caption | render piechart

Following is another quick query is to find Windows build numbers available in SCCM. You can check the different build numbers available in your SCCM infra. I don’t know how much useful information is this. However, if you run this query against all Windows 10 devices collection, then it’s useful for sure.

OperatingSystem | summarize count() by BuildNumber | render piechart
CMPivot Query to Find Windows 10 Devices from SCCM
CMPivot Query to Find Windows 10 Devices from SCCM

Resources

Leave a Comment

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