Jaa

SCCM Report To Get All Microsoft office versions

  • Artikkeli

SCCM Report To Get All Microsoft office versions

We’re back with new and interesting post by Manoj Kumar.

Many times you get a request to track your software deployment progress.  An example, you are working on new office version deployment using SCCM and you want to track the progress on install office count. 

Here’s a quick post about a custom report that I have created to get the all office versions installed on SCCM clients. One can use same query for other software installed in you environment, you just need to change the display name of the product. To get the software title use this  table “add_remove_programs_data”

select

count(Total.Name0) as Total_Systems,COUNT(office2003.Name0) as MSoffice2003,COUNT(Office2007.Name0) as MSOffice2007,COUNT(Office2010.Name0) as MsOffice2010

From

(select vr.Name0 from v_R_System vr)

as Total

left join

(select distinct sys.Name0,

      displayname00 as OfficeEdition,

      version00 as OfficeVersion from system_disc sys

left join

add_remove_programs_data arp on sys.itemkey = arp.machineid where

(displayname00 = 'Microsoft Office Basic Edition 2003'

OR displayname00 = 'Microsoft Office Excel 2003'

OR displayname00 = 'Microsoft Office Outlook 2003'

OR displayname00 = 'Microsoft Office Personal Edition 2003'

OR displayname00 = 'Microsoft Office Professional Edition 2003'

OR displayname00 = 'Microsoft Office Small Business Edition 2003'

OR displayname00 = 'Microsoft Office Standard Edition 2003')

and client0 = 1)as office2003

on Total.Name0=office2003.Name0

left join

(Select distinct sys.Name0,

      displayname00 as OfficeEdition,

      version00 as OfficeVersion from system_disc sys

left join

add_remove_programs_data arp on sys.itemkey = arp.machineid where

(displayname00 like 'Microsoft Office Personal 2007'

or displayname00 like 'Microsoft Office Professional 2007'

or displayname00 like 'Microsoft Office Professional 2007 Trial'

or displayname00 like 'Microsoft Office Professional Hybrid 2007'

or displayname00 like 'Microsoft Office Professional Plus 2007'

or displayname00 like 'Microsoft Office Professional Plus 2007 (Beta)'

or displayname00 like 'Microsoft Office Standard 2007'

or displayname00 like 'Microsoft Office Standard 2007 Trial'

or displayname00 like 'Microsoft Office Ultimate 2007')

and sys.client0 = 1)as Office2007

on Total.Name0=office2007.Name0

left join

(select distinct sys.Name0,

      displayname00 as OfficeEdition,

      version00 as OfficeVersion from system_disc sys

left join

add_remove_programs_data arp on sys.itemkey = arp.machineid where

(displayname00 like 'Microsoft Office 2010'

or displayname00 like 'Microsoft Office Professional Plus 2010'

or displayname00 like 'Microsoft Office Standard 2010'

or displayname00 like 'Microsoft Office Professional 2010'

or displayname00 like 'Microsoft Office Home and Student 2010'

or displayname00 like 'Microsoft Office Home and Business 2010'

or displayname00 like 'Microsoft Office Professional Plus 2010 (Beta)'

or displayname00 like 'Microsoft Office Starter 2010 - English')

and sys.client0 = 1) as office2010

on Total.Name0=office2010.Name0

Happy Reading

Comments

  • Anonymous
    January 01, 2003
    Awesome, was looking for something like this. Thank you.
  • Anonymous
    August 15, 2014
    Thanks for your help.

    Please help me out to find out the office 2010 and 2013 version with service pack installed report In SCCM 2012.
  • Anonymous
    August 15, 2014
    If possible please send me the details on my mail ID varunashat@gmail.com
  • Anonymous
    August 21, 2014
    @varun, you can use following sql query to find the all version of office in your environment and add those office 2013 version to previous query.



    select distinct sys.Name0,
    displayname00 as OfficeEdition,
    version00 as OfficeVersion from system_disc sys
    left join

    add_remove_programs_data arp on sys.itemkey = arp.machineid where

    (displayname00 like '%Microsoft Office%')

  • Anonymous
    October 28, 2014
    Keep getting tis error (SCCM 2007 report): The SELECT permission was denied on the object 'Add_Remove_Programs_DATA', database

    What am I doing wrong here?