Office Channel

Office Channel.sql

    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