Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Report / SQL Help

Created: 29 Apr 2010 • Updated: 31 Oct 2010 | 6 comments

Hey guys,

I'm not proficient at all when it comes to SQL and I was wanting to add a number count to a previous report that was built by a former employee.  Basically this is a report for Add / Remove Programs without the install numbers, I have other reports that have the install numbers but not the vendor.  Any sort of help or advice would be greatly appreciated.  I'm sure this is an easy addition but once again I couldn't even tell you where to start.  Anyhow, this is what I have at this point:

Level 0:

SELECT DISTINCT   

 
car.[Name] [Application Name],  
car.[Version],  
car.[Publisher]  
FROM dbo.vComputer i  
JOIN dbo.Inv_AeX_OS_Add_Remove_Programs_spt a1  
ON a1.[_ResourceGuid] = i.Guid  
JOIN dbo.Cmn_OS_Add_Remove_Programs_Common car  
ON car.[_KeyHash] = a1.[_KeyHash]  
JOIN dbo.Inv_AeX_AC_Identification d  
ON d.[_ResourceGuid] = i.Guid  
JOIN dbo.CollectionMembership cm  
ON cm.ResourceGuid = d.[_ResourceGuid]  
JOIN dbo.vCollection it  
ON it.Guid = cm.CollectionGuid  
WHERE car.[Name] LIKE '%ApplicationName%'  
AND d.[System Type] LIKE 'Win%'  
AND i.[Name] LIKE '%ComputerName%'  
AND d.Domain LIKE '%Domain%'  
AND it.[Guid] = '%Collection%' 
AND car.[Hidden] = 'False'

Level 1

SELECT DISTINCT     

 
i.[Name] [Name],   
car.[Name] [Application Name],   
car.[Version],   
car.[Publisher]   
FROM dbo.vComputer i   
JOIN dbo.Inv_AeX_OS_Add_Remove_Programs_spt a1   
ON a1.[_ResourceGuid] = i.Guid   
JOIN dbo.Cmn_OS_Add_Remove_Programs_Common car   
ON car.[_KeyHash] = a1.[_KeyHash]   
JOIN dbo.Inv_AeX_AC_Identification d   
ON d.[_ResourceGuid] = i.Guid   
JOIN dbo.CollectionMembership cm   
ON cm.ResourceGuid = d.[_ResourceGuid]   
JOIN dbo.vCollection it   
ON it.Guid = cm.CollectionGuid   
WHERE REPLACE(REPLACE(car.[Name], '[', ''), ']', '') LIKE REPLACE(REPLACE('%Application Name%', '[', ''), ']', '')   
AND d.[System Type] LIKE 'Win%'   
AND i.[Name] LIKE '%ComputerName%'   
AND d.Domain LIKE '%Domain%'   
AND it.[Guid] = '%Collection%'  
AND car.[Version] LIKE '%Version%' 
AND car.[Hidden] = 'False'

Comments 6 CommentsJump to latest comment

dfrancis's picture

I thought this query looked like something I saw within the last couple of weeks..

So do you want to be able to see a report with a list of applications and the install count?

i.e.
Microsoft Office Visio Professional 2003  |  11.0.8173.0  |  Microsoft Corporation  | 25
to see that you have 25 copies of Visio 2003 installed in your environment?

Then drill into that row to see a list of all computers with Visio 2003?

--Dave

If a forum post solves your problem, please flag it as a solution. If you like an article, blog post or download vote it up.

Broadway's picture

That is correct dfrancis.  I've actually gotten fairly close to what I'm looking for.  I might not actually need any help as I've been editing a copy of that one you helped me with already.

Broadway's picture

Looks like I figured it out!  Thanks for your interest regardless!

dfrancis's picture

Glad I could retroactively help! :)

--Dave

If a forum post solves your problem, please flag it as a solution. If you like an article, blog post or download vote it up.

Broadway's picture

Dave,

Apparently what I've gotten is exactly working like I'd hoped do you have any thoughts?  An install count to my original query is all I'm looking for.

Shobud's picture

Hi,
Not sure why you are joining all those tables but not using the info that's in them so I stripped it down to this,
which will give you what you want:

SELECT COUNT(a1.[Name]),a1.[Name] AS [Application Name],

a1.[Version],

a1.[Publisher]

FROM dbo.vComputer i

JOIN dbo.Inv_AeX_OS_Add_Remove_Programs a1

ON a1.[_ResourceGuid] = i.Guid

GROUP BY a1.[Name],a1.[Version], a1.[Publisher]

Just re-insert your WHERE clause conditions with the variables which I removed for testing purposes.

Dan