Solved

SCCM 2012 Database Query:  Office 2010 Service Packs

Posted on 2014-12-01
6
1,493 Views
Last Modified: 2014-12-01
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
0
Comment
Question by:Wolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40473533
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
 
LVL 1

Author Comment

by:Wolf
ID: 40473608
When I run that i get this message:

Incorrect syntax near the keyword 'INNER'
0
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40473627
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:Wolf
ID: 40473702
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
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40473726
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
 
LVL 1

Author Comment

by:Wolf
ID: 40473780
Many thanks for your help, very much appreciated
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question