Part 1(https://endpointmanagers.com/hardware-and-software-assessment-with-configmgr-part-1) talked about the Devices
Part 2(https://endpointmanagers.com/hardware-and-software-assessment-with-configmgr-part-2) talked about the Operating System, now we’ll look at the Office 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 Office.sql query.
- Enable the Inventory of Excluded Office 365 apps. Check out how http://eskonr.com/2020/11/use-sccm-to-find-the-excluded-apps-in-microsoft-365-apps-or-office-365-proplus-for-custom-reporting/
- Open PowerShell and execute Invoke-RFLO365VersionToBuildMap.ps1 script (that is available on my github repository). This will create the SQL Insert Statement so we can know the build information of all Office 365 installations. I use it like .\Invoke-RFLO365VersionToBuildMap.ps1 -GenerateInsertTableSQLStatement | Out-File c:\Temp\O365TableVariable.txt
- 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
- Create a database called CM_SupportData
- Change the Database to CM_SupportData
- Open the O365TableVariable.txt created by the script and execute it
- Change the Database to your ConfigMgr database and open the Office.sql file
- Change the @CollectionID to the collection you want to filter and click Execute
- Add or remove a ProductNames used by Office under the line left join fn_rbac_GS_OFFICE_PRODUCTINFO(‘disabled’) officepi on officepi.ResourceID = rsy.ResourceID and officepi.ProductName0 in (‘Microsoft Office Professional Plus 2016’, ‘Microsoft Office Standard 2016’, ‘Microsoft Office Standard 2013’, ‘Microsoft Office Professional Plus 2013’, ‘Microsoft 365 – en-us’, ‘Microsoft Office 365 ProPlus – en-us’, ‘Microsoft Office Professional Plus 2010’, ‘Microsoft Office Professional Plus 2019 – en-us’, ‘Microsoft 365 Apps for enterprise – en-us’,’Microsoft 365 for enterprise – en-us’)
- 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:
- Office Versions Analysis
- Office Edition Analysis
- Office Architecture Analysis
- Office 365 Channel Analysis
- Office 365 Build Analysis
- Office 365 State Analysis
- Office 365 Version Analysis
- Office 365 Excluded Product Analysis
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
- Office Versions Analysis: Drag the OfficeVersion field to Rows
- Office Edition Analysis: Drag the ProductName field to Rows
- Office Architecture Analysis: Drag the Architecture field to Rows
- Office 365 Channel Analysis: Drag the O365Channel to Rows, OfficeVersion to Filters and filter it by 365
- Office 365 Build Analysis: Drag the O365BuildNumber to Rows, OfficeVersion to Filters and filter it by 365
- Office 365 State Analysis: Drag the O365Status to Rows, OfficeVersion to Filters and filter it by 365
- Office 365 Version Analysis: Drag the ProductVersion to Rows, OfficeVersion to Filters and filter it by 365
- Office 365 Excluded Product Analysis: Drag the O365ExcludedProduct to Rows, OfficeVersion to Filters and filter it by 365
Part 1: https://endpointmanagers.com/hardware-and-software-assessment-with-configmgr-part-1
Part 2: https://endpointmanagers.com/hardware-and-software-assessment-with-configmgr-part-2
Article originally published on LinkedIn.
