Finding Office in v_Add_Remove_Programs

Having over 12 million rows in v_Add_Remove_Programs makes queries take several minutes, which displeases folks when trying to report on it.  I’ve looked at the data, as well as Microsoft’s published product code IDs to create a data mart for legacy Office installations; this specifically looks for Office 2000, 2003, 2007, 2010, and 2013; as we are rolling out 2016 Click to Run.  Machines with Office 2016 currently installed are excluded in the query used on the report.

Use the following in a SQL Agent job to cull the data into a table in a separate database:

IF OBJECT_ID(‘dbo.cmLegacyOfficeProducts’, ‘U’) IS NOT NULL
DROP TABLE dbo.cmLegacyOfficeProducts;
GO

with LegacyOfficeProducts (ResourceID, ProdID0, DisplayName0, InstallDate0, Publisher0, Version0)
as
(
— Office 2000 https://support.microsoft.com/en-us/help/230848/description-of-the-numbering-scheme-for-the-product-code-guids-in-office-2000
select arp.ResourceID, arp.ProdID0, arp.DisplayName0, arp.InstallDate0, arp.Publisher0, arp.Version0 from CM_NYS.dbo.v_Add_Remove_Programs arp
where ProdID0 like ‘{[0-F][0-F][0-4][0-F]____-78E1-11D2-B60F-006097C998E7}’
and arp.DisplayName0 not like ‘Microsoft FrontPage %’
union all
— Office 2003 https://support.microsoft.com/en-us/help/832672/description-of-numbering-scheme-for-product-code-guids-in-office-2003
select arp.ResourceID, arp.ProdID0, arp.DisplayName0, arp.InstallDate0, arp.Publisher0, arp.Version0 from CM_NYS.dbo.v_Add_Remove_Programs arp
where arp.ProdID0 like ‘{[0-F][0-2][1-3][0-9]____-6000-11D3-8CFE-0150048383C9}’
and arp.DisplayName0 like ‘Microsoft Office%2003%’
and arp.DisplayName0 not like ‘Microsoft FrontPage %’
union all
— Office 2007 https://support.microsoft.com/en-us/help/928516/description-of-product-code-guids-in-2007-office-suites-and-programs
select arp.ResourceID, arp.ProdID0, arp.DisplayName0, arp.InstallDate0, arp.Publisher0, arp.Version0 from CM_NYS.dbo.v_Add_Remove_Programs arp
where arp.ProdID0 like ‘{[0-F][0-F]12[0-9][0-9][0-9][0-9]-[0-F][0-F][0-F][0-F]-[0-F][0-F][0-F][0-F]-[0,1]000-[0,1]000000FF1CE}’
and arp.DisplayName0 like ‘Microsoft Office%2007%’
and arp.DisplayName0 not like ‘Microsoft Office%MUI (%’
and arp.DisplayName0 not like ‘Microsoft Office Proof%’
and arp.DisplayName0 not like ‘Microsoft Office%Server%’
and arp.DisplayName0 not like ‘Microsoft Office %Viewer 2007%’
and arp.DisplayName0 not like ‘Microsoft Office 2007 Primary Interop Assemblies’
and arp.DisplayName0 not like ‘Microsoft Office Visual Web Developer 2007’
and arp.DisplayName0 not like ‘Microsoft Office Office 64-bit Components 2007’
union all
— Office 2010 https://support.microsoft.com/en-us/help/2186281/description-of-the-numbering-scheme-for-product-code-guids-in-office-2010
select arp.ResourceID, arp.ProdID0, arp.DisplayName0, arp.InstallDate0, arp.Publisher0, arp.Version0 from CM_NYS.dbo.v_Add_Remove_Programs arp
where arp.ProdID0 like ‘{[0-F][0-F]14[0-9][0-9][0-9][0-9]-[0-F][0-F][0-F][0-F]-[0-F][0-F][0-F][0-F]-[0,1]000-[0,1]000000FF1CE}’
and arp.DisplayName0 like ‘Microsoft Office%2010%’
and arp.DisplayName0 not like ‘Microsoft Office%MUI (%’
and arp.DisplayName0 not like ‘Microsoft Office Proof%’
and arp.DisplayName0 not like ‘Microsoft Office%Server%’
and arp.DisplayName0 not like ‘Microsoft Office 2010 Primary Interop Assemblies’
and arp.DisplayName0 not like ‘Microsoft Office Single Image 2010’
and arp.DisplayName0 not like ‘Microsoft Office ScreenTip Language 2010 – English’
and arp.DisplayName0 not like ‘Microsoft Office IME (Chinese (Simplified)) 2010’
and arp.DisplayName0 not like ‘Microsoft Office 2010%’
and arp.DisplayName0 not like ‘Microsoft Office Office 32-bit Components 2010’
and arp.DisplayName0 not like ‘Microsoft Office Office 64-bit Components 2010’
union all
— Office 2013 https://support.microsoft.com/en-us/help/2786054/description-of-the-numbering-scheme-for-product-code-guids-in-office-2013
select arp.ResourceID, arp.ProdID0, arp.DisplayName0, arp.InstallDate0, arp.Publisher0, arp.Version0 from CM_NYS.dbo.v_Add_Remove_Programs arp
where arp.ProdID0 like ‘{[0-F][0-F]15[0-9][0-9][0-9][0-9]-[0-F][0-F][0-F][0-F]-[0-F][0-F][0-F][0-F]-[0,1]000-[0,1]000000FF1CE}’
and (arp.DisplayName0 like ‘Microsoft Office%2013%’ or arp.DisplayName0 like ‘Microsoft Project%2013%’ or arp.DisplayName0 like ‘Microsoft Visio%2013%’)
and arp.DisplayName0 not like ‘Microsoft Office%MUI (%’ and arp.DisplayName0 not like ‘Microsoft Project%MUI (%’ and arp.DisplayName0 not like ‘Microsoft Visio%MUI (%’
and arp.DisplayName0 not like ‘Microsoft Office Proof%’
and arp.DisplayName0 not like ‘Microsoft Office %2013 -%’
and arp.DisplayName0 not like ‘Microsoft Visio Viewer 2013’
and arp.DisplayName0 not like ‘Microsoft Visio SDK 2013’
and arp.DisplayName0 not like ‘Microsoft Office 32-bit Components 2013’
and arp.DisplayName0 not like ‘Microsoft Office 64-bit Components 2013’
)
select * into cmLegacyOfficeProducts from LegacyOfficeProducts;
GO

Then use the following in a report:

select distinct
s.Full_Domain_Name0
, s.Name0
, scu.TopConsoleUser0
, u.Mail0
, lopo.DisplayName0 OfficeDisplayName
, lopo.InstallDate0 OfficeInstallDate
, lopo.Publisher0   OfficePublisher
, lopo.Version0     OfficeVersion
, lopv.DisplayName0 VisioDisplayName
, lopv.InstallDate0 VisioInstallDate
, lopv.Publisher0   VisioPublisher
, lopv.Version0     VisioVersion
, lopp.DisplayName0 ProjectDisplayName
, lopp.InstallDate0 ProjectInstallDate
, lopp.Publisher0   ProjectPublisher
, lopp.Version0     ProjectVersion
from CM_NYS.dbo.v_R_System s
left join (select ResourceID, max (TimeStamp) maxTimeStamp from CM_NYS.dbo.v_GS_SYSTEM_CONSOLE_USAGE group by ResourceID) lcur on lcur.ResourceID = s.ResourceID
left join CM_NYS.dbo.v_GS_SYSTEM_CONSOLE_USAGE scu on scu.ResourceID = lcur.ResourceID and scu.TimeStamp = lcur.maxTimeStamp
left join CM_NYS.dbo.v_R_User u on u.Unique_User_Name0 = scu.TopConsoleUser0
left join CM_Supplemental.dbo.cmLegacyOfficeProducts lopo on s.ResourceID = lopo.ResourceID and
(
lopo.displayname0 like ‘Microsoft Office %Premium%’
or lopo.displayname0 like ‘Microsoft Office %Professional%’
or lopo.displayname0 like ‘Microsoft Office %Standard%’
or lopo.displayname0 like ‘Microsoft Office %Enterprise%’
or lopo.displayname0 like ‘Microsoft Office %Ultimate%’
) and
lopo.displayname0 not like ‘%visio%’ and lopo.displayname0 not like ‘%project%’
left join CM_Supplemental.dbo.cmLegacyOfficeProducts lopv on s.ResourceID = lopv.ResourceID and lopv.displayname0 like ‘%visio%’
left join CM_Supplemental.dbo.cmLegacyOfficeProducts lopp on s.ResourceID = lopp.ResourceID and lopp.displayname0 like ‘%project%’
where Client0 = 1 and Client_Version0 > ‘5’ and s.Full_Domain_Name0 in (@DomainList)
and Operating_System_Name_and0 like ‘Microsoft Windows NT Workstation%’
and s.ResourceID not in (select ResourceID from CM_NYS.dbo.v_GS_OFFICE365PROPLUSCONFIGURATIONS where VersionToReport0 is not null)
order by
s.Full_Domain_Name0
, s.Name0

NOTE: v_GS_OFFICE365PROPLUSCONFIGURATIONS.VersionToReport0 will have data even if only one O365 product is installed, such as Visio or Project… doesn’t necessarily mean the suite is installed.

Full_Domain_Name0 is a parameter to filter the data.

Capture.JPG

For completeness:

https://support.microsoft.com/en-us/help/3120274/description-of-the-numbering-scheme-for-product-code-guids-in-office-2016

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s