Office Version Reports with SQL on SCCM

We should write;

Just How Many Have

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 Standard 2013%' or arp.displayname0 like '%Microsoft Office Enterprise 2013%' or arp.displayname0 like '%Microsoft Office Professional%2013%' 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

With Who Use

SELECT 
 S.Name0, 
 S.SystemRole0, 
 S.SystemType0, 
 S.Domain0, 
 INSO.ARPDisplayName0 
FROM 
 v_GS_INSTALLED_SOFTWARE INSO 
 INNER JOIN v_GS_SYSTEM S ON INSO.ResourceID = S.ResourceID 
where 
 ARPDisplayName0 like '%Office professional%'

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir