Skip to content

Instantly share code, notes, and snippets.

@danjpadgett
Last active April 20, 2018 13:49
Embed
What would you like to do?
Office Channel
SELECT sys.Name0 [Computer Name] ,
"Update Channel" =
CASE
When O365.CDNBaseUrl00 = 'http://officecdn.microsoft.com/pr/7ffbc6bf-bc32-4f92-8982-f9dd17fd3114' Then 'Semi-Annual Channel'
When O365.CDNBaseUrl00 = 'http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60' Then 'Monthly Channel'
When O365.CDNBaseUrl00 = 'http://officecdn.microsoft.com/pr/b8f9b850-328d-4355-9145-c59439a0c4cf' Then 'Semi-Annual Channel (Targeted)'
When O365.CDNBaseUrl00 = 'http://officecdn.microsoft.com/pr/64256afe-f5d9-4f86-8936-8840a6a4f5be' Then 'Monthly Channel (Targeted)'
End
,
o365.LastScenario00 [Last Scenario],
o365.LastScenarioResult00 [Last Result],
O365.VersionToReport00 [Version Number],
"Shared Computer Activation" =
Case
When O365.SharedComputerLicensing00 = '1' Then 'Yes'
Else 'No'
End
,
o365.UpdatesEnabled00 [Updates Enabled],
o365.TimeKey [Status Time]
FROM dbo.Office365ProPlusConfigurations_DATA O365
join v_R_System sys on O365.MachineID = sys.ResourceID
where o365.VersionToReport00 IS NOT NULL
--------------------------------------
-Get User information
SELECT DISTINCT
SYS.Name0
,ARP.DisplayName0 As 'Software Name'
,ARP.Version0 As 'Version'
,SYS.User_name0 As 'User Name'
,Usr.Full_User_Name0 As 'Display Name',
"Update Channel" =
CASE
When O365.CDNBaseUrl00 = 'http://officecdn.microsoft.com/pr/7ffbc6bf-bc32-4f92-8982-f9dd17fd3114' Then 'Semi-Annual Channel'
When O365.CDNBaseUrl00 = 'http://officecdn.microsoft.com/pr/492350f6-3a01-4f97-b9c0-c7c6ddf67d60' Then 'Monthly Channel'
When O365.CDNBaseUrl00 = 'http://officecdn.microsoft.com/pr/b8f9b850-328d-4355-9145-c59439a0c4cf' Then 'Semi-Annual Channel (Targeted)'
When O365.CDNBaseUrl00 = 'http://officecdn.microsoft.com/pr/64256afe-f5d9-4f86-8936-8840a6a4f5be' Then 'Monthly Channel (Targeted)'
End
FROM
dbo.v_R_System As SYS
INNER JOIN dbo.v_FullCollectionMembership FCM On FCM.ResourceID = SYS.ResourceID
INNER JOIN dbo.v_Add_REMOVE_PROGRAMS As ARP On SYS.ResourceID = ARP.ResourceID
INNER JOIN dbo.v_r_user As USR On SYS.User_name0 = USR.User_name0
Inner JOIN dbo.Office365ProPlusConfigurations_DATA as O365 on O365.MachineID = sys.ResourceID
WHERE
(ARP.DisplayName0 LIKE 'Microsoft Office 365 ProPlus - en-us')
ORDER BY Name0 ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment