Hardware and Software Assessment with ConfigMgr – Part 2

Part 1(https://endpointmanagers.com/hardware-and-software-assessment-with-configmgr-part-1) talked about the Devices, now we’ll look at the Operating System Analysis and as always, all queries can be found at my github repository at https://github.com/dotraphael/ConfigMgr_SQL_HW_SW_Assessment.

In this post, I’ll be using the operatingsystem.sql query.

Start by opening the Microsoft SQL Server Management. If you don’t have it installed, download it from https://aka.ms/ssmsfullsetup
Connect to the ConfigMgr server
Change the Database to your ConfigMgr database and open the operatingsystem.sql file
Change the @CollectionID to the collection you want to filter and click Execute
In the result, select all and then right click and click Copy with headers
Open in excel and past the results
On the Home tab, click Format as table and select the format.
On the Format as Table, confirm the My Table has headers and click Ok
On the Insert tab, click Pivot Table
On the Pivot Table, select the Add this data to the Data Model and click Ok
On the New Sheet, select Insert tab and then Recommended Charts.
Select the chart template and click Ok

Now we’re almost ready. We have our source of details as well as a way to visualise the data and now it is time to play with the content.

With the return of the device query, we can analyse the following:

So to do this you’ll need:

On the PivotChart Fields drag the ResourceID to the Values twice. For both of them, click on the arrow and select Value Field Settings and then select Distinct count under summarise value field by
For the 2nd, change to the Show values as tab and select % of grand total under Show value as
Drag the SCCM Managed and LastInventory to filters. Filter the SCCM Managed to Enabled and the LastInventory unselect the Never

Now it is time to look at each individual item

Windows Versions Analysis: Drag the Operating System Version field to Rows
Windows Edition Analysis: Drag the Operating System Edition field to Rows
Windows Architecture Analysis: Drag the OS Architecture field to Rows
Windows 10 Branches: Drag the OSBranchName field to Rows, Operating System Version to Filters and filter it by Windows 10
Windows 10 Versions: Drag the BuildNumberDescription field to Rows, Operating System Version to Filters and filter it by Windows 10
Windows 10 State: Drag the State field to Rows, Operating System Version to Filters and filter it by Windows 10

Article originally published on LinkedIn.