Wolf
asked on
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_SMSInstalledSi tes 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_syste m_name_and 0 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
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_SMSInstalledSi
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_syste
group BY arp.DisplayName0, arp.version0
ORDER BY arp.DisplayName0, arp.version0
Any idea how I can get this information simply?
Thanks
ASKER
When I run that i get this message:
Incorrect syntax near the keyword 'INNER'
Incorrect syntax near the keyword 'INNER'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.
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.
ASKER
Many thanks for your help, very much appreciated
Check if this query it's somehow faster:
Open in new window