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'
Discussion Filed Under:
Comments 6 Comments • Jump to latest comment
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
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.
Looks like I figured it out! Thanks for your interest regardless!
Glad I could retroactively help! :)
--Dave
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.
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
Would you like to reply?
Login or Register to post your comment.