SQL Reports: Microsoft Office Suites

The difficulty in creating a report from the out-of-box reports in ConfigMgr is the wonderful array of product names that Microsoft uses. That is, one size doesn't fit all. You can't just use LIKE 'Microsoft Office%':

• Microsoft Office 97 Professional Edition
• Microsoft Office 365 ProPlus - en-us
• Microsoft Office 365 ProPlus - ko-kr
• Microsoft Office OSM MUI (English) 2013
• Microsoft Office OSM UX MUI (English) 2013
• Microsoft Office Shared Setup Metadata MUI (English) 2013
• Microsoft Office Shared 64-bit Setup Metadata MUI (English) 2013
• Microsoft Office 64-bit Components 2013
• Microsoft Office Shared 64-bit MUI (English) 2013
• Microsoft Office Home and Business 2010
• Microsoft Office Professional Edition 2003
• Microsoft Office Professional Edição 2003

In a word: Urgh! So how do we report on count of all the version installed? Firstly, you should get a report of all the versions that your estate has installed. Run a query against your CM DB and export to a file:

SELECT [ResourceID]

      ,[GroupID]

      ,[TimeStamp]

      ,[ARPDisplayName0]

      ,[InstallDate0]

      ,[InstallDirectoryValidation0]

      ,[InstalledLocation0]

      ,[InstallSource0]

      ,[InstallType0]

      ,[Language0]

      ,[LocalPackage0]

      ,[MPC0]

      ,[OsComponent0]

      ,[PackageCode0]

      ,[ProductID0]

      ,[ProductName0]

      ,[ProductVersion0]

      ,[Publisher0]

      ,[RegisteredUser0]

      ,[ServicePack0]

      ,[SoftwareCode0]

      ,[UninstallString0]

      ,[UpgradeCode0]

      ,[VersionMajor0]

      ,[VersionMinor0]

  FROM [CM_I01].[dbo].[v_GS_INSTALLED_SOFTWARE] WHERE ARPDisplayName0 like 'Microsoft Office%'

Now you need to prune out all the individual product names (Excel, Powerpoint, Outlook), Proofing, Shared Components etc). This I did exporting the query to Excel and applying a filter. Took about 7 minutes. This will leave you with the Product list for the Office suites themselves:

Microsoft Office 97 Professional Edition

Microsoft Office 365 ProPlus - en-us

Microsoft Office 365 ProPlus - ko-kr

Microsoft Office Home and Business 2010

Microsoft Office 365 ProPlus - de-de

Microsoft Office Professional Edition 2003

Microsoft Office Professional Edição 2003

Microsoft Office Standard Edition 2003

Microsoft Office Enterprise 2007

Microsoft Office Professional Plus 2007

Microsoft Office Professional Plus 2010

Microsoft Office Standard 2010

Microsoft Office Home and Student 2010

Microsoft Office Click-to-Run 2010

Microsoft Office Starter 2010 - English

Microsoft Office Professional Plus 2013

Microsoft Office Standard 2013

Microsoft Office Professional Plus 2016

Microsoft Office XP Professional

Microsoft Office 2000 SR-1 Professional

Now you can get to the good stuff. The SQL report. In my report I want to know all the suites installed and reported on from the start of 2016, grouped by count and sorted by descending value. I won't go through how to create a report and how to pretty is up, but here's the query. You should paste in your own results for the suites you're looking for: 

Select ARPDisplayName0 AS "Office Product Suite", count (*) AS "Count" FROM (

SELECT  

       [ResourceID]

      ,[GroupID]

      ,[TimeStamp]

      ,[ARPDisplayName0]

       ,[InstallDate0]

      ,[InstallDirectoryValidation0]

      ,[InstalledLocation0]

      ,[InstallSource0]

      ,[InstallType0]

      ,[Language0]

      ,[LocalPackage0]

      ,[MPC0]

      ,[OsComponent0]

      ,[PackageCode0]

      ,[ProductID0]

      ,[ProductName0]

      ,[ProductVersion0]

      ,[Publisher0]

      ,[RegisteredUser0]

      ,[ServicePack0]

      ,[SoftwareCode0]

      ,[UninstallString0]

      ,[UpgradeCode0]

      ,[VersionMajor0]

      ,[VersionMinor0]

  FROM [CM_I01].[dbo].[v_GS_INSTALLED_SOFTWARE]

  WHERE convert(varchar(10),timestamp,121)>='2016-01-01' AND ARPDisplayName0 IN (

  'Microsoft Office 97, Professional Edition',

  'Microsoft Office 365 ProPlus - en-us',

  'Microsoft Office 365 ProPlus - ko-kr',

  'Microsoft Office Home and Business 2010',

  'Microsoft Office 365 ProPlus - de-de',

  'Microsoft Office Professional Edition 2003',

  'Microsoft Office Professional Edição 2003',

  'Microsoft Office Standard Edition 2003',

  'Microsoft Office Enterprise 2007',

  'Microsoft Office Professional Plus 2007',

  'Microsoft Office Professional Plus 2010',

  'Microsoft Office Standard 2010',

  'Microsoft Office Home and Student 2010',

  'Microsoft Office Click-to-Run 2010',

  'Microsoft Office Starter 2010 - English',

  'Microsoft Office Professional Plus 2013',

  'Microsoft Office Standard 2013',

  'Microsoft Office Professional Plus 2016',

  'Microsoft Office XP Professional',

  'Microsoft Office 2000 SR-1 Professional')) a

group by ARPDisplayName0

order by count (*) DESC

Management are very happy bunnies, and your SQL-Fu has improved by +2