Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SCCM 2012 Database Query:  Office 2010 Service Packs

Posted on 2014-12-01
6
Medium Priority
?
1,800 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
  • 3
  • 3
6 Comments
 
LVL 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 52

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Microsoft has released remote PowerShell capabilities to all commercial Office 365 customers. So you can be controlled via PowerShell and not from the Office 365 admin center Download Windows PowerShell Module for Lync Online http://www.micros…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

824 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