SCCM 2016: list of computers
Let’s consider several ways to get the list of computers in the MS Configuration Manager v1806 latest version.
Method 1: Create Device Collections
A group of computers can display not only the number of devices, but their names. To create a collection of computers, go to Assets and Compliance->Overview->Device Collections, right-click and in the menu, select the item Create Device Collection - the wizard opens. In the first step we give the collection a name and choose a group from the list of all computers All Systems.
In the second step Membership Rules in the drop-down list, select the second item Query Rule, which means creating a rule based on a WQL query.
In the new window that opens, give the name of the rule, click the button Edit Query Statement, go to the tab Criteria and press the first button with the sun icon (which means Create). In the first drop-down list, select the item Simple Value and click the button Select and in the window that appears in the first drop-down list, select the item System Resource, select attribute Operating System Name and Version and specify the value Microsoft Windows NT Workstation 6.1.
Thus we will see the number of machines based on Windows 7. If you need to create more groups, similar selection rules are made and other version values are selected (5.1 – Windows XP, 6.1 – Windows 7, 6.3 – Windows 8, 10.0 – Windows 10) [https://en.wikipedia.org/wiki/List_of_Microsoft_Windows_versions].
To see which computers exactly belong to this collection, you need to right-click on it and select Show Members in the context menu.
Method 2. Create an SQL query
First you need to connect to the database with some SQL editor. For start, 2 views will suffice: v_GS_Operating_System and v_GS_System.
From v_GS_System we take the names of computers and from v_GS_Operating_System get the bit depth and Windows version.
In the SQL query, we join tables by unique number ResourceID and *MachineID * and make a selection of the required fields:
SELECT dbo.System_DATA.Name0, dbo.System_DATA.SystemRole0, dbo.System_DATA.SystemType0, dbo.v_GS_OPERATING_SYSTEM.Caption0, dbo.v_GS_OPERATING_SYSTEM.Version0
FROM dbo.System_DATA LEFT OUTER JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.System_DATA.MachineID dbo.v_GS_OPERATING_SYSTEM.ResourceID
WHERE (dbo.System_DATA.SystemRole0 NOT LIKE '%Server%')
ORDER BY dbo.System_DATA.Name0
Method 3. Create a report
SCCM has a considerable number of ready-made reports. Enter *Monitoring->Overview->Reporing->Reports * and open report group – Operating System or we can go to the browser http://b-sql/ReportServer
* *
Computers with a specific system report will display the names of computers and their OS versions and their disk location and Count Operating system versions report will show total number of computers regarding the OS version.
If you want to display more data e.g. memory size, hard disk capacity, OS version, user’s full name, cabinet/office number then a simple report (and a SQL query from one table) is not enough.
Method 4. Use CMPivot
CMPivot programm [/ru-ru/sccm/core/servers/manage/cmpivot] embedded in SCCM console can also be useful to display real-time information about devices, OS version, architecture, free physical memory status.
To obtain the necessary data, you need to right-click on the collection of All Systems devices and select Start CMPivot in the context menu. In the left part of the program, the available parameters will be displayed as a tree and in the right - field to write a query and display the result. At the top of the program we will write a simple query:
OS | where (Version == '10.0.17134') or
OS | where (Version like '10%') or
OS | where (Version like ‘%workstation%10%’).
** **
Method 5. Display all
Sometimes it is convenient to display the entire list of user’s machines for the visibility of the full picture, rather than navigate through the collections.
To display the full list of computers, it is enough to perform only one action: being in Assets and Compliance->Overview->Devices right-click on the panel and select Operation System.