Hardware and Software Assessment with ConfigMgr – Part 1

Happy new year all. Last year we did a 10 part series about Managing devices with Microsoft Endpoint Manager (and there are more about this topic to come). But this year, lets start talking about Configuration Manager.

There are many companies out there that manage their device state and keep a good hardware and software inventory, however, there are many more companies that do not have this and any migration to a new operating system or application take ages to be completed and never without many issues.

As a consultant, I see this in most engagements. So in this series of posts I’ll help you to maximise the usage of ConfigMgr with many queries to collect and analyse data. All queries can be found at my github repository at https://github.com/dotraphael/ConfigMgr_SQL_HW_SW_Assessment.

To start this series, I’ll be using the devices.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 Devices.sql file
  • Change the @CollectionID to the collection you want to filter and click Execute
  • I’m ignoring some of the Virtual Machine models. If you need to add or remove, check out the line left join fn_rbac_GS_COMPUTER_SYSTEM(‘disabled’) cs on cs.ResourceID = rsy.ResourceID and cs.Model0 not in (‘Virtual Machine’, ‘VMware7,1’)
  • 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:

  • Client Installed Analysis
  • Inventory Analysis
  • Hardware Model Analysis
  • Windows 10 Requirements (based on pure Windows 10 requirements)
  • Office 365 Requirements (based on pure Office 365 requirements)
  • Recommended Hardware Upgrade (based on what I believe should be the minimum requirement for Windows 10, Office 365, Internet and mail usage)

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

Now it is time to look at each individual item

  • Client Installed Analysis: Drag the SCCM Managed field to Rows
  • Inventory Analysis: Drag the last Inventory field to Rows
  • Hardware Model Analysis: Drag the Model field to Rows, SCCM Managed and LastInventory to filters. Filter the SCCM Managed to Enabled and the LastInventory unselect the Never
  • Windows 10 Requirements (based on pure Windows 10 requirements): Drag the Windows 10 Readiness field to Rows, SCCM Managed and LastInventory to filters. Filter the SCCM Managed to Enabled and the LastInventory unselect the Never
  • Office 365 Requirements (based on pure Office 365 requirements): Drag the Office 365 Readiness field to Rows, SCCM Managed and LastInventory to filters. Filter the SCCM Managed to Enabled and the LastInventory unselect the Never
  • Recommended Hardware Upgrade (based on what I believe should be the minimum requirement for Windows 10, Office 365, Internet and mail usage): Drag the Recommended Readiness field to Rows, SCCM Managed and LastInventory to filters. Filter the SCCM Managed to Enabled and the LastInventory unselect the Never

Article originally published on LinkedIn.

Similar Posts

Leave a Reply

Your email address will not be published.