Joseph Hinkle at MyITForum.com

Steve Thompson wrote a post about sub-select queries that brought to mind a limitation of SQL that I wasn't aware of until recently. All of our Office installations are based on the Professional MSI. We install the 'Standard' components, and add Access and Publisher if a Pro license was purchased. This procedure was recently discontinued, but we still have machines out there and they're difficult to report on. The question of how to tell if they were Standard or Professional was raised. The quick answer was 'Do they have Access?'.

First, I used this query, much like Steve's:

CREATE VIEW dbo.v_Systems_w_MSOFFICE
AS
select distinct
SYS.Name0,
SYS.User_Name0,
SYS.AD_Site_Name0,
CASE
when sys.name0 in (select * from v_Systems_w_msAccess) then
CASE
when ADDREM.DisplayName0 like '%97%' then 'Microsoft Office 97 Professional'
when ADDREM.DisplayName0 like '%2000%' then 'Microsoft Office 2000 Professional'
when ADDREM.DisplayName0 like '%2003%' then 'Microsoft Office 2003 Professional'
when ADDREM.DisplayName0 like '%XP%' then 'Microsoft Office XP Professional'
when ADDREM.DisplayName0 like '%2007%' then 'Microsoft Office 2007 Professional Plus'
else addrem.DisplayName0
END
else
CASE
when ADDREM.DisplayName0 like '%97%' then 'Microsoft Office 97 Standard'
when ADDREM.DisplayName0 like '%2000%' then 'Microsoft Office 2000 Standard'
when ADDREM.DisplayName0 like '%XP%' then 'Microsoft Office XP Standard'
when ADDREM.DisplayName0 like '%2003%' then 'Microsoft Office 2003 Standard'
when ADDREM.DisplayName0 like '%2007%' then 'Microsoft Office 2007 Standard'
else addrem.DisplayName0
END
End as 'OfficeVer',
case
when ADDREM.Version0 = '9.00.9327' then 'SP3'
when ADDREM.Version0 = '10.0.2627.01' then 'SP0'
when ADDREM.Version0 = '10.0.6626.0' then 'SP3'
when ADDREM.Version0 = '11.0.5614.0' then 'SP0'
when ADDREM.Version0 = '11.0.6361.0' then 'SP1'
when ADDREM.Version0 = '11.0.7969.0' then 'SP2'
when ADDREM.Version0 = '11.0.8173.0' then 'SP3'
when ADDREM.Version0 like '12.0.4518%' then 'SP0'
when ADDREM.Version0 like '12.0.6215%' then 'SP1'
when ADDREM.Version0 = '9.00.2720' then 'SP1'
when ADDREM.Version0 = '9.00.3821' then 'SP2'
end as 'ServicePack'
from
v_R_System as SYS,
v_GS_ADD_REMOVE_PROGRAMS as ADDREM
where
SYS.ResourceID = ADDREM.ResourceID
AND SYS.Client0 = 1
AND ADDREM.DisplayName0 like 'Microsoft Office%'
AND ADDREM.DisplayName0 not like '%(%'
AND ADDREM.DisplayName0 not like '%runtime%'
AND ADDREM.DisplayName0 not like '%interop%'
AND ADDREM.DisplayName0 not like '%proofing%'
AND ADDREM.DisplayName0 not like '%converter%'
AND ADDREM.DisplayName0 not like '%viewer%'
AND ADDREM.DisplayName0 not like '%visio%'
AND ADDREM.DisplayName0 not like '%project%'
AND ADDREM.DisplayName0 not like '%disc 2%'
AND ADDREM.DisplayName0 not like '%sounds%'
AND ADDREM.DisplayName0 not like '%media%'
AND ADDREM.DisplayName0 not like '%meeting%'
AND ADDREM.DisplayName0 not like '%onenote%'
AND ADDREM.DisplayName0 not like '%template%'
AND ADDREM.DisplayName0 not like '%accelerator%'
AND ADDREM.DisplayName0 not like '%components%'
AND ADDREM.DisplayName0 not like '%frontpage 2003'

The question then became 'How many of each?'. As much as I wanted to tell the user export it and do a PivotTable in Excel, that just wouldn't fly. At this point, I hit a wall. The subselect can't be used in a GROUP BY statement. The answer is really simple: make a view from the query above.

Add a new view in the SMS database. Make sure that you grant the webreport_approle select permissions on the new view. After that you can base all of your queries on the new view.

To do a group by version:

select
officever as 'Office Version',
count (*) as 'Count'
from v_Systems_w_MSOFFICE
group by officeVer
order by Officever

To group by version and service pack level:

select
officever as 'Office Version',
servicepack as 'Service Pack',
count (*) as 'Count'
from v_Systems_w_MSOFFICE
group by officeVer, ServicePack
order by Officever, ServicePack

This solution is a little 'dirty', but it works quite well. Performance in SQL isn't always great when you're doing nested CASE statements with sub-selects, but it gave me the report I was looking for. If anyone can think of a better way, post it in the comments!