Trying to combine or simplify two scripts into one that will give me MSFT Office on our factory machines

Question

First query:

select distinct s.Name0 as [Computer Name], 
s.User_Name0 as [Last Logon user], 
os.Caption0 as [Operating System],
u.Full_User_Name0 as [Full User Name],
s.AD_Site_Name0 as [Site]
from v_R_System s
full outer join v_R_User u on u.User_Name0 like s.User_Name0
full outer join v_GS_OPERATING_SYSTEM os on os.ResourceID = s.ResourceID
where s.Active0 = 1
and os.Caption0 not like '%server%' 
and (os.Caption0 like '%Enterprise%' 
or os.Caption0 like '%Entreprise%' 
or os.Caption0 like '%Professional%' 
or os.Caption0 like '%Standard%'
or os.Caption0 like '%Pro%' 
or os.Caption0 like '%Ultimate%') 
and (s.name0 like '______F%'
or s.name0 like '______K%'
or s.Distinguished_Name0 like '%factory%'
or s.Distinguished_Name0 like '%kiosk%')

Second query:

select distinct arp.displayname0 as 'Product', rsys.name0 as 'Computer', 
sos.caption0 as 'OS'
from v_Add_Remove_Programs arp
join v_R_System rsys
join v_gs_operating_system sos
on sos.resourceid = rsys.resourceid
on rsys.ResourceID = arp.ResourceID
where arp.displayname0 like 'Microsoft Office Professional Plus%'
and Operating_System_Name_and0 like 'microsoft windows nt workstation%'
and arp.displayname0 not like 'Microsoft Office Professional Plus 2013 - en-
us'
and arp.displayname0 not like '%update%'
and rsys.Active0 = 1
group by arp.displayname0, rsys.name0, sos.caption0 
order by rsys.name0, arp.displayname0, sos.caption0 

My attempt:

from v_R_System s
full outer join v_R_User u on u.User_Name0 like s.User_Name0
full outer join v_GS_OPERATING_SYSTEM os on os.ResourceID = s.ResourceID
full outer join v_Add_Remove_Programs arp on arp.ResourceID = arp.ResourceID
where s.Active0 = 1
and os.Caption0 not like '%server%' 
and (os.Caption0 like '%Enterprise%' 
or os.Caption0 like '%Entreprise%' 
or os.Caption0 like '%Professional%' 
or os.Caption0 like '%Standard%'
or os.Caption0 like '%Pro%' 
or os.Caption0 like '%Ultimate%') 
and (s.name0 like '______F%'
or s.name0 like '______K%'
or s.Distinguished_Name0 like '%factory%'
or s.Distinguished_Name0 like '%kiosk%'
and arp.DisplayName0 like 'Microsoft Office Professional Plus%')

My attempt is running wayyy too long, so I have to kill it before I know if it's even working. I need something that will essentially show me all office on machines meeting the criteria of the first query. I'm sure there are inefficiencies and redundancies (and inaccuracies) in my query of the SCCM database. All help is much appreciated!

Answer 1

To combine this two query, I will use where resourceID in (secondary query). See below:

select distinct 
arp.displayname0 as 'Product', 
rsys.name0 as 'Computer', 
rsys.User_Name0 as [Last Logon user],
sos.caption0 as 'OS',
u.Full_User_Name0 as [Full User Name],
rsys.AD_Site_Name0 as [Site]
from v_Add_Remove_Programs arp
join v_R_System rsys on rsys.ResourceID = arp.ResourceID
join v_gs_operating_system sos on sos.resourceid = rsys.resourceid
full outer join v_R_User u on u.User_Name0 like rsys.User_Name0
where arp.displayname0 like 'Microsoft Office Professional Plus%'
and Operating_System_Name_and0 like 'microsoft windows nt workstation%'
and arp.displayname0 not like 'Microsoft Office Professional Plus 2013 - en-
us'
and arp.displayname0 not like '%update%'
and rsys.Active0 = 1
and rsys.resourceID in 
(select  
s.ResourceID
from v_R_System s
full outer join v_R_User u on u.User_Name0 like s.User_Name0
full outer join v_GS_OPERATING_SYSTEM os on os.ResourceID = s.ResourceID
where s.Active0 = 1
and os.Caption0 not like '%server%' 
and (os.Caption0 like '%Enterprise%' 
or os.Caption0 like '%Entreprise%' 
or os.Caption0 like '%Professional%' 
or os.Caption0 like '%Standard%'
or os.Caption0 like '%Pro%' 
or os.Caption0 like '%Ultimate%') 
and (s.name0 like '______F%'
or s.name0 like '______K%'
or s.Distinguished_Name0 like '%factory%'
or s.Distinguished_Name0 like '%kiosk%'))

group by arp.displayname0, rsys.name0, sos.caption0 , rsys.User_Name0, u.Full_User_Name0, rsys.AD_Site_Name0
order by rsys.name0, arp.displayname0, sos.caption0   

Above query should work well if both of your query work.

In my opinion, another way would be to build the first query as a collection in console, then you can use collectionID to filter all those systems.