none
SCCM query to find office 32 bit only and office 64 bit only systems RRS feed

  • Question

  • Hi,

    Need two queries.

    I have systems which have OS of 32 bit and OS of 64 bit done in two device collections.

    Need SCCM query to find office 32 bit only and office 64 bit only systems.

    Please assist.

    Wednesday, August 31, 2016 7:03 AM

Answers

  • Dear Sir,

    32bit Office will only in SMS_G_SYSTEM_ADD_Remove_Programs and 64bit ones are in  SMS_G_SYSTEM_ADD_Remove_Programs_64. See below one: (Just modified above ones and without any syntax check)

    x86 Office:

    SELECT DISTINCT sys.Name, SMS_G_SYSTEM_ADD_Remove_Programs .DisplayName
    FROM SMS_R_SYSTEM sys INNER JOIN SMS_G_SYSTEM_ADD_Remove_Programs arp ON sys.ResourceID = arp.ResourceID  
    WHERE arp.DisplayName LIKE '%Microsoft Office Standard%' or arp.DisplayName Like 'Microsoft Office Professional%' or arp.DisplayName LIke 'Microsoft Office Enterprise %'

    x64 Office:

    SELECT DISTINCT sys.Name, arp64.DisplayName
    FROM SMS_R_SYSTEM sys INNER JOIN SMS_G_SYSTEM_ADD_Remove_Programs_64 arp64 ON sys.ResourceID = arp64.ResourceID  
    WHERE arp64.DisplayName LIKE '%Microsoft Office Standard%' or arp64.DisplayName Like 'Microsoft Office Professional%' or arp64.DisplayName LIke 'Microsoft Office Enterprise %'

    Best regards

    Frank


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, September 2, 2016 9:27 AM
    Moderator

All replies

  • Have you seen https://social.technet.microsoft.com/Forums/en-US/47ec7d2f-5113-4bf6-93f3-c3e109789feb/sccm-sql-query-for-office-32-bit-and-64-bit?forum=configmanagergeneral ?
    Wednesday, August 31, 2016 7:17 AM
  • Yes, Seen that thread.

    However, that is an SQL query and I need a WQL query that can be used for device collections. :)

    Wednesday, August 31, 2016 7:27 AM
  • Yes, Seen that thread.

    However, that is an SQL query and I need a WQL query that can be used for device collections. :)

    Once you use the SQL query for create a collection based on the query, then SCCM will automatic convert that query into WQL, 

    Sharad Singh | My blogs: SharadTech | Twitter: @SinghSharaad | | Please remember to click “Mark as Answer” on the post that helps you.This can be beneficial to other community members reading the thread.

    Wednesday, August 31, 2016 7:42 AM
  • Once you use the SQL query for create a collection based on the query, then SCCM will automatic convert that query into WQL, 


    There is no supported way to create a collection based on SQL.

    Torsten Meringer | http://www.mssccmfaq.de

    Wednesday, August 31, 2016 8:17 AM
    Moderator
  • REALLY? Never knew that!

    Copied the below query to notepad and then pasted the same in SCCM device collection query.

    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 %'

    This is giving the error : The query has a syntax error. Are you sure you want to save it?

    Wednesday, August 31, 2016 9:11 AM
  • You have to change some syntax for example in sql you use v_R_system but when you are usng in collection query then change SMS_R_System 

    Sharad Singh | My blogs: SharadTech | Twitter: @SinghSharaad | | Please remember to click “Mark as Answer” on the post that helps you.This can be beneficial to other community members reading the thread.

    Wednesday, August 31, 2016 9:31 AM
  • This is giving the error : The query has a syntax error.


    That query is using SQL, but you have to use WQL for Collections instead.

    Torsten Meringer | http://www.mssccmfaq.de

    Wednesday, August 31, 2016 9:42 AM
    Moderator
  • You have to change some syntax for example in sql you use v_R_system but when you are usng in collection query then change SMS_R_System 

    Sharad Singh | My blogs: SharadTech | Twitter: @SinghSharaad | | Please remember to click “Mark as Answer” on the post that helps you.This can be beneficial to other community members reading the thread.


    there is more to it than changing just v_r_system.

    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased


    Wednesday, August 31, 2016 10:39 AM
    Moderator
  • Hi Garth,

    Thanks for the correction that it wont automatically change to WQL after pasting!

    Can you help with the WQL queries please?

    Wednesday, August 31, 2016 10:57 AM
  • Can you help with the WQL queries please?

    Just as Torsten suggested, what is wrong with using the existing collections? What exact are you hoping to do with this query?

    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased

    Wednesday, August 31, 2016 12:16 PM
    Moderator
  • Dear Sir,

    32bit Office will only in SMS_G_SYSTEM_ADD_Remove_Programs and 64bit ones are in  SMS_G_SYSTEM_ADD_Remove_Programs_64. See below one: (Just modified above ones and without any syntax check)

    x86 Office:

    SELECT DISTINCT sys.Name, SMS_G_SYSTEM_ADD_Remove_Programs .DisplayName
    FROM SMS_R_SYSTEM sys INNER JOIN SMS_G_SYSTEM_ADD_Remove_Programs arp ON sys.ResourceID = arp.ResourceID  
    WHERE arp.DisplayName LIKE '%Microsoft Office Standard%' or arp.DisplayName Like 'Microsoft Office Professional%' or arp.DisplayName LIke 'Microsoft Office Enterprise %'

    x64 Office:

    SELECT DISTINCT sys.Name, arp64.DisplayName
    FROM SMS_R_SYSTEM sys INNER JOIN SMS_G_SYSTEM_ADD_Remove_Programs_64 arp64 ON sys.ResourceID = arp64.ResourceID  
    WHERE arp64.DisplayName LIKE '%Microsoft Office Standard%' or arp64.DisplayName Like 'Microsoft Office Professional%' or arp64.DisplayName LIke 'Microsoft Office Enterprise %'

    Best regards

    Frank


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, September 2, 2016 9:27 AM
    Moderator
  • Hi Raghu,

    Hi find the below query, hope it will help you.

    Office 32
    ===============
    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 %'
    ===================

     

    ===============
    Office 64
    ===============
    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 %'
    ===================

    Thanks,

    Harvansh Singh

    Friday, September 2, 2016 10:17 AM
  • Hi Raghu,

    Hi find the below query, hope it will help you.

    Office 32
    ===============
    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 %'
    ===================

     

    ===============
    Office 64
    ===============
    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 %'
    ===================

    Thanks,

    Harvansh Singh


    both of these are sql queries not wql queries that are needed by collections. Franks queries are wql.

    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased

    Friday, September 2, 2016 10:55 AM
    Moderator
  • Thanks Garth, I will share soon WQL query..

    Thanks,

    Harvansh Singh

    Saturday, September 3, 2016 7:30 AM
  • I will share soon WQL query..


    ok but what is wrong with Frank's queries? Why re-invent the wheel? I have personaly test his x64 query and it works as expected.

    Garth Jones

    Blog: http://www.enhansoft.com/blog Old Blog: http://smsug.ca/blogs/garth_jones/default.aspx

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleased


    Saturday, September 3, 2016 2:00 PM
    Moderator
  • Hi Garth, you are right there is no wrong with Frank, Just want check in environment if there is any alternate way to write query. 

    Thanks,

    Harvansh Singh

    Saturday, September 3, 2016 5:37 PM
  • Add Office365ProPlusConfiguration to your hardware inventory classes, then you can use this query:

    For x86

    select SMS_R_System.ResourceId, SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client, SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.VersionToReport, SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.Platform from  SMS_R_System inner join SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS on SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.Platform = "x86"

    For x64

    select SMS_R_System.ResourceId, SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client, SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.VersionToReport, SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.Platform from  SMS_R_System inner join SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS on SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_OFFICE365PROPLUSCONFIGURATIONS.Platform = "x64"
    Wednesday, August 7, 2019 2:45 PM