Solved

SCCM 2012 Database Query:  Office 2010 Service Packs

Posted on 2014-12-01
6
1,541 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 50

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 50

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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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 50

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need return values from a stored procedure 8 64
SQL Server Shrink hurting performance? 4 49
How to keep a record with the highest value 3 59
grouping by date only 6 22
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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