I know you've heard it before, but...I need drill down help please!

sixdollarshirt · 06-25-2009, 09:33 AM

I have searched high and low trying to find out what I'm doing wrong here and I just can't get it. I'm trying to run a report that shows how many versions of which Adobe products are installed on our clients. I am able to get that information with the following query.

Code:

SELECT a.Name, a.Version, COUNT(a.name) AS '# Installed' 
FROM [inv_aex_os_add_remove_programs] a 
WHERE [publisher] LIKE '%adobe%' 
GROUP BY a.Name, a.version 
ORDER BY a.Name

What I would like to do is then double click on an application that is returned from that query and see which machines have that particular application installed. I attempted to do it by altering this code I found slightly.

Code:

select ag.[_ResourceGuid], i.[Name], tcp.[IP Address]   
from Inv_Aex_Ac_Client_Agent ag   
join vComputer i on ag.[_Resourceguid] = i.[Guid]   
join Inv_AeX_AC_TCPIP tcp on ag.[_ResourceGuid] = tcp.[_ResourceGuid]   
join Inv_AeX_OS_Add_Remove_Programs adp on adp.[_ResourceGuid] = ag.[_ResourceGuid]   
where adp.[publisher] LIKE '%adobe%'
order by i.[Name] asc

The problem is that the above query returns a row for EVERY application that is installed on a client.

I'm sure this is something easy, but I can't get it right now and would truly appreciate any help or advice you may have to offer.

Thanks in advance for your help.

sixdollarshirt · 06-25-2009, 10:38 AM

OK, I'm getting closer.

Here is my first query:

Code:

SELECT a.[Name] AS Name, a.Version, COUNT(a.name) AS '# Installed'   
FROM [inv_aex_os_add_remove_programs] a   
WHERE publisher LIKE '%adobe%'   
GROUP BY Name, a.version   
ORDER BY Name

I pass "Name" as a parameter in the drop down to this query:

Code:

select ag.[_ResourceGuid], i.[Name], tcp.[IP Address]     
from Inv_Aex_Ac_Client_Agent ag     
join vComputer i on ag.[_Resourceguid] = i.[Guid]     
join Inv_AeX_AC_TCPIP tcp on ag.[_ResourceGuid] = tcp.[_ResourceGuid]     
join Inv_AeX_OS_Add_Remove_Programs adp on adp.[_ResourceGuid] = ag.[_ResourceGuid]     
where adp.[Name] = %Name%  
order by i.[Name] asc

This works pretty well except I'm getting several rows returned for each client the application is installed on and I'm not sure why.

Does anything here look out of place? Thanks in advance for your help again. I appreciate it.

Brandon · 06-25-2009, 11:10 AM

Ok think about the second query. After looking at that, and substituting a generic %Name% into it, how would it know you want a specific version? You are asking it to list all ARP for adobe on that machine. Adobe has published Acrobat, Reader, flash, etc. You need to pass the version as well as the name. Using Inv_Aex_Ac_Client_Agent ag isn't necessary, just join to the i.Guid.

EDIT:

I am really confused following this. The alias's have diff names and I keep getting turned around. It is just easier if I paste one:

Basic Global Param Prompts

Code:

App with default value of %

Level0

Code:

select T0.Name,T0.[Version], count(*) AS 'Total #'       
 from Inv_AeX_OS_Add_Remove_Programs T0        
 Inner join vComputer T1        
 on T0.[_Resourceguid] = T1.[guid]           
 WHERE T0.[Name] LIKE '%App%'    
Group by T0.[Name], T0.[Version]

Configure Drilldown to Lvl 1

Code:

Name|Version

Level 1

Code:

select T1.[Name], T1.[User], T1.[ip address] from Inv_AeX_OS_Add_Remove_Programs T0                          
 INNER JOIN vComputer T1                          
  ON T0.[_ResourceGuid] = T1.[guid]      
 WHERE T0.[Name] LIKE '%Name%' AND   
                       T0.[Version] LIKE '%Version%'       
ORDER BY T1.[Name]

sixdollarshirt · 06-25-2009, 03:33 PM

Thanks for the help. Here is what I ended up doing:

Level 0

Code:

SELECT T0.[Name] AS Name, T0.[Version] AS Version, count(*) as 'Total #'  
FROM Inv_AeX_OS_Add_Remove_Programs T0  
JOIN vComputer T1  
on T0.[_ResourceGuid] = T1.[guid]  
WHERE T0.[Name] LIKE '%adobe%'  
GROUP BY T0.[Name],T0.[Version]  
ORDER BY T0.[Name]

Drilldown to Level 1

Code:

Name|Version

Level 1

Code:

SELECT T1.[Name] AS Name, T0.[_ResourceGuid],T1.[IP Address], T0.[Name] AS Application, T0.[Version]     
FROM Inv_AeX_OS_Add_Remove_Programs T0     
JOIN vComputer T1 on T0.[_ResourceGuid] = T1.[Guid]     
WHERE T0.[Name] = '%Name%' AND T0.[Version] = '%Version%'     
ORDER BY T1.[Name] ASC

It all works now. Thanks again for your assistance. I appreciate the help.