Monday, 29 June 2015

SCCM Query collection for All Office Versions

           ,      3 comments   


Office 2013:

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 on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceID where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Plus 2013"


Office 2010 :

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 on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceID where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Plus 2010"

Office 2007 :

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 on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceID where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Enterprise 2007"

Office 2003 :

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 on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceID where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Edition 2003"

3 comments:

  1. BryanP24 August 2015 at 05:48

    These are a good starting point, but for beginners you should point out that each variation of office needs to be individually searched for, and if you (like me) have a mix of users with the 32 bit and 64 bit versions of office they'll need to query those separately as well. I recently had to look for any Office 2010 installs that were below service pack 2, and this is what I did. Do you know of a better / easier way to do this?

    The collection query below looks for every variation of Office 2010 32-bit below SP2. For 64-bit installs, replace SMS_G_System_ADD_REMOVE_PROGRAMS with SMS_G_System_ADD_REMOVE_PROGRAMS_64


    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 on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office 2010" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "14.0.7015.1000" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Plus 2010" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "14.0.7015.1000" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Standard 2010" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "14.0.7015.1000" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional 2010" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "14.0.7015.1000" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Home and Student 2010" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "14.0.7015.1000" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Home and Business 2010" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "14.0.7015.1000" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Plus 2010 (Beta)" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "14.0.7015.1000" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Starter 2010 - English" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "14.0.7015.1000"

    ReplyDelete
    Replies
    1. G.1 September 2015 at 01:45

      Thanks for pointing out. I'll try to update scripts as new products and complex configs are coming out.

      Delete
  2. John-Eric Clements29 March 2016 at 07:59

    I have a slightly different approach, and I was wondering about your take. This searches all programs with "Office" in the name, and I've been adding exclusions as I run into them. What I'd like to know is if I can easily filter by domain, and what the best way to do it would be?

    SELECT
    arp.DisplayName0 AS 'Office Version',
    arp.Publisher0 AS 'Publisher',
    COUNT (DISTINCT sys.ResourceID) AS 'Client Count',
    (STR((
    COUNT(sys.ResourceID)*100.0/(
    SELECT COUNT(arp.ResourceID)

    FROM v_Add_Remove_Programs arp
    INNER JOIN v_R_System sys ON arp.ResourceID=sys.ResourceID

    WHERE
    (
    arp.DisplayName0 LIKE '%Office%'
    OR
    arp.DisplayName0 LIKE '%Visio%'
    OR
    arp.DisplayName0 LIKE '%Project%'
    )
    AND
    (
    arp.DisplayName0 NOT LIKE '%Viewer%'
    AND
    arp.DisplayName0 NOT LIKE '%Service Pack%'
    AND
    arp.DisplayName0 NOT LIKE '%Update%'
    AND
    arp.DisplayName0 NOT LIKE '%Proof%'
    AND
    arp.DisplayName0 NOT LIKE '%Pack%'
    AND
    arp.DisplayName0 NOT LIKE '%Click-to-Run%'
    AND
    arp.DisplayName0 NOT LIKE '%File Validation%'
    AND
    arp.DisplayName0 NOT LIKE '%32-Bit Components%'
    AND
    arp.DisplayName0 NOT LIKE '%Communications Server%'
    AND
    arp.DisplayName0 NOT LIKE '%Shared%'
    AND
    arp.Publisher0 LIKE '%Microsoft%'
    )
    AND
    sys.Client0=1
    AND
    ((sys.Obsolete0=0) OR (sys.Obsolete0 is not null)))),5,2)) + ' %' AS 'Percent %'

    FROM
    v_Add_Remove_Programs arp
    INNER JOIN v_R_System sys ON arp.ResourceID=sys.ResourceID

    WHERE
    (
    arp.DisplayName0 LIKE '%Office%'
    OR
    arp.DisplayName0 LIKE '%Visio%'
    OR
    arp.DisplayName0 LIKE '%Project%'
    )
    AND
    (
    arp.DisplayName0 NOT LIKE '%Viewer%'
    AND
    arp.DisplayName0 NOT LIKE '%Service Pack%'
    AND
    arp.DisplayName0 NOT LIKE '%Update%'
    AND
    arp.DisplayName0 NOT LIKE '%Proof%'
    AND
    arp.DisplayName0 NOT LIKE '%Pack%'
    AND
    arp.DisplayName0 NOT LIKE '%Click-to-Run%'
    AND
    arp.DisplayName0 NOT LIKE '%File Validation%'
    AND
    arp.DisplayName0 NOT LIKE '%32-Bit Components%'
    AND
    arp.DisplayName0 NOT LIKE '%Communications Server%'
    AND
    arp.DisplayName0 NOT LIKE '%Shared%'
    AND
    arp.Publisher0 LIKE '%Microsoft%'
    )

    GROUP BY
    arp.DisplayName0,
    arp.Publisher0
    ORDER BY
    COUNT (DISTINCT sys.ResourceID) DESC

    ReplyDelete

Give me you feedback!