Reporting on Office 365 Channels with SQL Query

Estimated reading time: 4 minutes

If you’re responsible for managing Office 365 deployments within your organization, having visibility into which channels your devices are using is crucial for effective administration and maintenance. With the help of SQL queries, you can extract pertinent information from Configuration Manager data, allowing you to generate insightful reports or visualize data using tools like Power BI.

In this blog post, we’ll provide you with an SQL query tailored for reporting on Office 365 channels, empowering you to gain actionable insights into your deployment.

The Office 365 Channel SQL Query

SELECT DISTINCT
SYS.Netbios_Name0 AS 'Device',
COMP.Manufacturer0 AS 'Make',
COMP.Model0 AS 'Model',
CASE
WHEN O365.CDNBaseUrl00 LIKE '%f2e724c1-748f-4b47-8fb8-8e0d210e9208%' THEN 'Office 2019 Perpetual Enterprise Channel'
WHEN O365.CDNBaseUrl00 LIKE '%5030841d-c919-4594-8d2d-84ae4f96e58e%' THEN 'Office 2021 Perpetual Enterprise Channel'
WHEN O365.CDNBaseUrl00 LIKE '%7983bac0-e531-40cf-be00-fd24fe66619c%' THEN 'Office 2024 Perpetual Enterprise Channel'
WHEN O365.CDNBaseUrl00 LIKE '%7ffbc6bf-bc32-4f92-8982-f9dd17fd3114%' THEN 'Semi-Annual Enterprise Channel'
WHEN O365.CDNBaseUrl00 LIKE '%55336b82-a18d-4dd6-b5f6-9e5095c314a6%' THEN 'Monthly Enterprise Channel'
WHEN O365.CDNBaseUrl00 LIKE '%492350f6-3a01-4f97-b9c0-c7c6ddf67d60%' THEN 'Current Channel'
WHEN O365.CDNBaseUrl00 LIKE '%2e148de9-61c8-4051-b103-4af54baffbb4%' THEN 'Office 2019 Perpetual Enterprise Channel'
WHEN O365.CDNBaseUrl00 LIKE '%b8f9b850-328d-4355-9145-c59439a0c4cf%' THEN 'Semi-Annual Enterprise Channel (Preview)'
WHEN O365.CDNBaseUrl00 LIKE '%64256afe-f5d9-4f86-8936-8840a6a4f5be%' THEN 'Current Channel (Preview)'
WHEN O365.CDNBaseUrl00 LIKE '%5440fd1f-7ecb-4221-8110-145efaa6372f%' THEN 'Beta Channel'
ELSE 'Unknown'
END AS [Channel],
O365.CDNBaseUrl00 AS [Channel URL],
O365.VersionToReport00 AS [Version]
FROM dbo.v_R_System SYS
INNER JOIN v_GS_INSTALLED_SOFTWARE PROD ON SYS.ResourceID = PROD.ResourceID
INNER JOIN v_GS_OPERATING_SYSTEM OS ON OS.Resourceid = SYS.Resourceid
INNER JOIN v_GS_COMPUTER_SYSTEM COMP ON SYS.ResourceID = COMP.ResourceID
INNER JOIN Office365ProPlusConfigurations_DATA O365 ON SYS.ResourceID = O365.MachineID
WHERE cdnbaseurl00 IS NOT NULL

Understanding the Query

• Device: This column represents the name of the device where Office 365 is installed.
• Make: Refers to the manufacturer of the device.
• Model: Indicates the model of the device.
• Channel: Shows the Office 365 update channel being used by the device. The query uses a CASE statement to map CDN base URLs to specific channel names.
• Channel URL: Provides the URL associated with the update channel.
• Version: Represents the version of Office 365 being reported by the device.

Glossary

• Configuration Manager: A systems management software product by Microsoft for managing large groups of computers running Windows operating systems.
• SQL Query: Structured Query Language (SQL) is a standard language for accessing and manipulating databases. An SQL query is a command used to retrieve data from a database.
• Power BI: A business analytics tool by Microsoft that provides interactive visualizations and business intelligence capabilities.

FAQ (Frequently Asked Questions)

Conclusion

By running this query periodically, you can monitor the distribution of Office 365 channels across your organization, identify outliers, and ensure that devices are receiving updates from the intended channels.