SCCM 2012 Database Query: Office 2010 Service Packs

Hello

I need to find out how many computers in the network are running Office 2010 and which service pack.

SCCM 2012
SQL 2008

Software inventory is turned on, and I can see that there are approx 1100 systems with Office 2012, but it doesn't show the service pack information.

I've run the following query directly on the database but so far its been "debugging query" for over 30 minutes

SELECT arp.DisplayName0, case
      when arp.version0 LIKE '11.0.6361.0' then 'SP1' when arp.version0 LIKE '11.0.7969.0' then 'SP2' when arp.version0 LIKE '11.0.8173.0' then 'SP3' when arp.version0 LIKE '12.0.6215.1000' then 'SP1' when arp.version0 LIKE '12.0.6425.1000' then 'SP2'  when arp.version0 LIKE '12.0.6612.1000' then 'SP3' when arp.version0 LIKE '14.0.6029.1000' then 'SP1'
  else ''
  end as 'Service Pack', arp.Version0, Count(DISTINCT v_r_system.resourceid) AS 'Count'
FROM v_ADD_REMOVE_PROGRAMS arp, v_r_system, V_RA_System_SMSInstalledSites ASSG
WHERE ARP.resourceid =  v_r_system.resourceid and v_r_system.resourceid = assg.resourceid and
(arp.displayname0 like '%Microsoft Office%edition%' or arp.displayname0 like '%Microsoft Office Standard 2007%' or arp.displayname0 like '%Microsoft Office Enterprise 2007%' or arp.displayname0 like '%Microsoft Office Professional%2007%' or arp.displayname0 like '%Microsoft Office Standard 2010%' or arp.displayname0 like '%Microsoft Office Enterprise 2010%' or arp.displayname0 like '%Microsoft Office Professional%2010%' or arp.displayname0 like 'Microsoft Office 2000%' or arp.displayname0 like 'Microsoft Office XP%')and arp.displayname0 not like '%update%'  and arp.displayname0 not like '%Microsoft Office XP Web Components' and v_r_system.operating_system_name_and0 not like '%server%' and (InstallDate0 not like 'NULL')
group BY arp.DisplayName0,  arp.version0
ORDER  BY arp.DisplayName0,  arp.version0


Any idea how I can get this information simply?

Thanks
LVL 1
WolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not surprised that it's taking that long. With all those LIKE % and OR this query shouldn't has a good performance for sure.
Check if this query it's somehow faster:
SELECT arp.DisplayName0, 
	case arp.version0
       when '11.0.6361.0' then 'SP1' 
       when '11.0.7969.0' then 'SP2' 
       when '11.0.8173.0' then 'SP3' 
       when '12.0.6215.1000' then 'SP1' 
       when '12.0.6425.1000' then 'SP2'  
       when '12.0.6612.1000' then 'SP3' 
       when '14.0.6029.1000' then 'SP1'
   else ''
   end as 'Service Pack', 
   arp.Version0, 
   Count(DISTINCT v_r_system.resourceid) AS 'Count'
 FROM v_ADD_REMOVE_PROGRAMS arp, 
	INNER JOIN v_r_system ON ARP.resourceid =  v_r_system.resourceid
	INNER JOIN V_RA_System_SMSInstalledSites ASSG ON v_r_system.resourceid = assg.resourceid 
 WHERE  arp.displayname0 like '%Microsoft Office%'
	and arp.displayname0 not like '%update%'
	and arp.displayname0 not like '%Microsoft Office XP Web Components'
	and v_r_system.operating_system_name_and0 not like '%server%'
	and (InstallDate0 not like 'NULL')
 GROUP BY arp.DisplayName0,  arp.version0
 ORDER  BY arp.DisplayName0,  arp.version0

Open in new window

0
WolfAuthor Commented:
When I run that i get this message:

Incorrect syntax near the keyword 'INNER'
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, copy/paste issue. Forgot to delete a comma. Here's the corrected script:
SELECT arp.DisplayName0, 
	case arp.version0
       when '11.0.6361.0' then 'SP1' 
       when '11.0.7969.0' then 'SP2' 
       when '11.0.8173.0' then 'SP3' 
       when '12.0.6215.1000' then 'SP1' 
       when '12.0.6425.1000' then 'SP2'  
       when '12.0.6612.1000' then 'SP3' 
       when '14.0.6029.1000' then 'SP1'
   else ''
   end as 'Service Pack', 
   arp.Version0, 
   Count(DISTINCT v_r_system.resourceid) AS 'Count'
 FROM v_ADD_REMOVE_PROGRAMS arp
	INNER JOIN v_r_system ON ARP.resourceid =  v_r_system.resourceid
	INNER JOIN V_RA_System_SMSInstalledSites ASSG ON v_r_system.resourceid = assg.resourceid 
 WHERE  arp.displayname0 like '%Microsoft Office%'
	and arp.displayname0 not like '%update%'
	and arp.displayname0 not like '%Microsoft Office XP Web Components'
	and v_r_system.operating_system_name_and0 not like '%server%'
	and (InstallDate0 not like 'NULL')
 GROUP BY arp.DisplayName0,  arp.version0
 ORDER  BY arp.DisplayName0,  arp.version0

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

WolfAuthor Commented:
Thanks for your help

I changed it a bit so it looks for '%Microsoft Office Standard%' and it gives me this result:

Microsoft Office Standard 2007            12.0.4518.1014      8
Microsoft Office Standard 2007      SP2      12.0.6425.1000      6
Microsoft Office Standard 2007      SP3      12.0.6612.1000      1
Microsoft Office Standard 2010            14.0.4763.1000      44
Microsoft Office Standard 2010      SP1      14.0.6029.1000      1069
Microsoft Office Standard 2010            14.0.7015.1000      37
Microsoft Office Standard Edition 2003      SP3      11.0.8173.0      2

Does that mean I have 1069 machines that are not running Office 2010 service pack 2?  Is there any way to get the list of those computers in SQL?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I am not aware of SCCM tables. Just gave you an help with the SQL query.
Don't you have a SCCM data model? Then you'll know which table as the computer names so you can get the information you want.
0
WolfAuthor Commented:
Many thanks for your help, very much appreciated
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server Apps

From novice to tech pro — start learning today.