Solved

SCCM 2012 Database Query:  Office 2010 Service Packs

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

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 51

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 51

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

617 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