SCCM SQL Query for Microsoft Office 32 Bit and 64 Bit
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
Hello Prajwal sir,
Could you please help with the query for Adobe Acrobat installed along with the 32/64 Bit version?
Thanks.
Hello Prajwal sir,
By running the SQL Query, got the data like from ARP entry which is having office application but it doesn’t shows whether it is 32 bit office or 64 bit office
What would be nice is to know what query to use to find Product Details in Resource Explorer. This is a way more accurate way of finding applications installed by both User and System.
Hi, how do can we each export installed software list which are in our SCCM boundary group.
Hi Prajwal Sir,
Please help me with sql query to find the status of WDS and ccmexec services running or not.
Can you explain what issue are you facing in your setup ?.
I just want to find the computer names on which WDS and CCMEXEC Services are running
yes it doesn’t work for me as well
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.
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
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%”
Yeah, thats not really going to give you an accurate report on the ‘bitness’ of the office version. I ran that query and the result was not accurate at all.