Blog on Microsoft and Other Technologies.

SCCM SQL Query for Microsoft Office 32 Bit and 64 Bit

4 389

This post contains the SCCM SQL query for Microsoft office 32 bit and 64 bit. Many organizations today use Microsoft office to simply the work. Listing out office versions installed on all machines is a big task. Using SQL queries it’s very easy to find out the Office versions. However note that these are the SQL queries and not the WQL queries. Here is a forum thread that lists the difference between SQL and WQL queries.

SCCM SQL Query for Microsoft Office 32 Bit and 64 Bit

The below query lists the 32 bit versions of office installed on machines.

SELECT DISTINCT v_GS_SYSTEM.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
FROM v_GS_SYSTEM INNER JOIN v_GS_ADD_REMOVE_PROGRAMS ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID  
WHERE (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIKE '%Microsoft % Standard%' or
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) Like 'Microsoft % Professional%' or
(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) LIke 'Microsoft % Enterprise %'

The below query lists the 64 bit versions of office installed on machines.

SELECT DISTINCT v_GS_SYSTEM.Name0, v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0
FROM v_GS_SYSTEM INNER JOIN v_GS_ADD_REMOVE_PROGRAMS_64 ON v_GS_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS_64.ResourceID  
WHERE (v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) LIKE '%Microsoft Office Standard%' or
(v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) Like 'Microsoft Office Professional%'or
(v_GS_ADD_REMOVE_PROGRAMS_64.DisplayName0) LIke 'Microsoft Office Enterprise %'

If you are looking for a query that lists both 32 bit and 64 bit versions of Microsoft office installed, here it is. The query also fetches and displays the installed date as well.

SELECT DISTINCT  
  SYS.Name0
  ,ARP.DisplayName0 As 'Software Name'
  ,ARP.Version0 As 'Version'
  ,ARP.InstallDate0 As 'Installed Date'
 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 
 WHERE   
 (ARP.DisplayName0 LIKE '%Microsoft % Standard%'
 OR ARP.DisplayName0 LIKE 'Microsoft % Professional%'
 OR ARP.DisplayName0 LIKE 'Microsoft % Enterprise %')
 ORDER BY Name0 ASC

 

4 Comments
  1. asad ulla says

    Hi Prajwal,

    This query seems to be not working for me and i got below error.

    “This query has a syntax error. Are you sure you want to save it”

    Please help me i am new to SCCM.

    Thanks
    asad

    1. Mahmoud Abdelrahman says

      Hello Asad, I have also tried this query and didn’t work for me. I am using this one now and it’s working.

      This will list 64bit of any Office ver installed.

      select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like “%Microsoft Office Professional Plus%”

  2. michael says

    yes it doesn’t work for me as well

    1. BonesInRuncorn says

      Hi, if you’re copying and pasting the query directly you’ll need to delete and re-enter the speech marks before and after the % as SCCM wont recognise them as a valid character.

Leave A Reply

Your email address will not be published.