SQL Query to Find Microsoft 365 Apps Details from SCCM

Let’s check the SQL query to find Microsoft 365 apps details. The SQL query is to find Microsoft 365 app details from Add Remove program entries. You can get display name, publisher, and version details from add remove programs.

You can also download the office 365 reports (RDL files) from the following links Microsoft 365 Office Client Management Reports Part 1 and Microsoft 365 App Dashboard. Microsoft changed the brand name of Office 365 to Microsoft 365 apps.

I have explained Microsoft 365 Version vs. Build Numbers in the following post. You can have more details about the build numbers of Microsoft 365. You can also check SCCM Report Office 365 Client Upgrade Readiness Dashboard.

SQL Query to Find Microsoft 365 Apps

Let’s check how to use SQL query to find Microsoft 365 Apps. You should have SQL Management Studio installed and read-only access to SCCM DB to run the following query. Also, make sure that you try to run these SQL queries in a staging environment before running against production DB.

• Open the SQL Management Studio.
• Connect your Database Engine.
• Right Click on your database CM_XXX and click on ‘New Query’
• Copy the following SQL query to find the report of Microsoft 365 Apps details.
• Click on the Execute button.

NOTE! Take a look at one of the Windows 10 or Windows 11 devices installed with Microsoft Apps 365 to confirm the Add Remove program entries. I could see two different entries in some devices. 1. Microsoft 365 Apps for Enterprise and 2. Microsoft Office 365 Pro Plus (OLD version).

You can use the following SQL query to find out the Device Name, User Name, AD Site, Domain, App Display Name, and Version details of Microsoft 365 Apps.

Select Distinct
v_R_System.Name0 as 'Machine',
v_R_System.User_Name0 as 'User Name',
v_R_System.AD_Site_Name0 as 'Location',
v_R_System.Resource_Domain_OR_Workgr0 as 'Domain',
v_Add_Remove_Programs.DisplayName0 as 'App Display Name',
v_Add_Remove_Programs.Version0 as 'App Version'
From v_R_System
Inner Join v_Add_Remove_Programs on v_R_System.ResourceID = v_Add_Remove_Programs.ResourceID 
Where v_Add_Remove_Programs.DisplayName0 Like 'Microsoft 365 Apps%'
and v_R_System.Active0 = '1'

Results

You can find the results of the query similar to the screenshot below. You can add additional details into the above SQL query to collect more details about the support state of Microsoft 365 Apps etc.

You can easily create a Custom SCCM Report for Microsoft 365 Apps using the following query. You can refer to Create Custom Report Using Report Builder…

Resources

• What’s difference between office 365 semi annual enterprise channel & office upgrade