ConfigMgr CMPivot Query to Find Windows 10 Version

You can use ConfigMgr CMPivot query to find Windows 10 version. This is a very useful post for anyone who wants to find Windows 10 version installed on your computers.

The CMPivot queries are pretty useful when you want to find critical information from your managed clients. I love the CMPivot because of the fact that it is so simple to get the information from your devices.

Lately I have been working on CMPivot queries a lot and I am publishing posts on this blog. I know the list is going to be lengthy but hey it’s going to help many. All these CMPivot queries can be safely run on the SCCM production server and you can also export the results or output to a file.

One of the challenges faced by IT admins is to find the count of Windows 10 machines and their builds and version. This is especially relevant when you want to perform Windows 10 in-place upgrade. It is pretty easy to create a Windows 10 device collection based on either builds or edition. However if you want to query the real time data, the CMPivot comes for help. The ConfigMgr CMPivot query to find Windows 10 version is really useful.

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.

We will run the built-in ConfigMgr CMPivot query to find Windows 10 version 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.

ConfigMgr CMPivot Query to Find Windows 10 Version

Let’s look at the steps to find Windows 10 version using ConfigMgr CMPivot query. Launch the ConfigMgr console and go to Assets and Compliance > Overview > Device Collections. Choose the device collection against which you want to run the CMPivot. Right click the device collection and click Start CMPivot.

ConfigMgr CMPivot Query to Find Windows 10 Version
ConfigMgr CMPivot Query to Find Windows 10 Version

Click the Query tab and enter the below query. The below CMPivot query finds the Windows 10 version. The below CMPivot query also finds the Windows XP, Windows 7 and Windows 8 machines. It can report the Windows 10 version up to 20H2. Anything greater than would be reported as Windows 10 other. If there is a new Windows 10 build, do not forget to add the build number and update the query.

Update – The build number of Windows 10 21H2 is 19044 and the below CMPivot query is updated.

OperatingSystem | where ProductType == 1 | project Edition=case( 
 BuildNumber == '2600', 'Windows XP', 
 BuildNumber like '760%', 'Windows 7', 
 BuildNumber == '9600', 'Windows 8', 
 BuildNumber == '10240', 'Windows 10 1507', 
 BuildNumber == '10586', 'Windows 10 1511', 
 BuildNumber == '14393', 'Windows 10 1607', 
 BuildNumber == '15063', 'Windows 10 1703', 
 BuildNumber == '16299', 'Windows 10 1709', 
 BuildNumber == '17134', 'Windows 10 1803', 
 BuildNumber == '17763', 'Windows 10 1809', 
 BuildNumber == '18362', 'Windows 10 1903', 
 BuildNumber == '18363', 'Windows 10 1909', 
 BuildNumber == '19041', 'Windows 10 2004',
 BuildNumber == '19042', 'Windows 10 20H2',
 BuildNumber == '19043', 'Windows 10 21H1',
 BuildNumber == '19044', 'Windows 10 21H2',
 BuildNumber > '19045', 'Windows 10 Other', 
 BuildNumber) 
 | summarize count() by Edition | order by count_ asc 
ConfigMgr CMPivot Query to Find Windows 10 Version
ConfigMgr CMPivot Query to Find Windows 10 Version

The ConfigMgr CMPivot query to find Windows 10 version has worked correctly. The Windows 10 device collection that I have has got 4 computers running Windows 10 20H2 version.

Since I have got a smaller lab setup, my output is a bit limited. When you have different Windows 10 builds, and you run the same query in your production setup, you shall get a lengthy output.

You can export the query output to a CSV file when the output contains long list of machines. In the top right corner of the CMPivot query tool, click Export and select Results to File.

ConfigMgr CMPivot Query to Find Windows 10 Version
ConfigMgr CMPivot Query to Find Windows 10 Version

2 Comments

  1. Avatar photo Brian Prenta says:

    Hello,

    How could I get this Query to also show the device name?

    1. OperatingSystem | where ProductType == 1 | project Device, Edition=case(
      BuildNumber == ‘2600’, ‘Windows XP’,
      BuildNumber like ‘760%’, ‘Windows 7’,
      .
      .
      .

Leave a Reply

Your email address will not be published. Required fields are marked *