Reporting on Office 365 Channels with SQL Query

Table of Contents

Read Time:3 Minute, 17 Second

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)

What is an Office 365 update channel?

The Office 365 update channel determines how frequently you receive updates for your Office 365 applications. There are different channels available, including Monthly Channel, Semi-Annual Channel (Targeted), and Semi-Annual Channel. The Monthly Channel receives monthly updates, while the Semi-Annual Channel (Targeted) receives updates twice a year, and the Semi-Annual Channel receives updates once a year.

How can I use the provided SQL query?

You can execute the SQL query within the SQL Management Console to retrieve data directly or integrate it into reporting solutions like Power BI. The query will help you generate reports on Office 365 channels used across your organization.

Can I customize the SQL query for my specific needs?

Yes, you can modify the SQL query according to your requirements. You can adjust the column selection, filtering criteria, or add additional conditions to tailor the query to your specific use case.

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.

About Post Author

Andrew Armstrong

Founder of TechyGeeksHome and Head Editor for over 15 years! IT expert in multiple areas for over 23 years. Sharing experience and knowledge whenever possible! Making IT Happen.

techygeeks@outlook.com

https://techygeekshome.info

administrator