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

Query Help: Pulling all installed Software from a specific collection

Created: 13 Jun 2012 | 2 comments

Good afternoon,

I have a query I've been using to pull installed software based on Domain and certain other criteria for a while now. It was meant to pull all data on machines on a given NS. I have been asked if I can now add in the ability to just pull the same results from a given collection.

I have attempted to join on tables like dbo.CollectionMembership and dbo.CollectionIncludeResource but cannot seem to get it to return what I need. below is the origional query without the additional join. Would anyone have any advice on how I can filter these results on a specific collection? (Note: I am running this over an NS 6 Altiris DB)

Thanks!

Here is the Query:

----------------------------------------------------------------------------

SELECT DISTINCT

i.[Name] [Name],

i.[Domain] [Domain],

car.[Name] [Application Name],

car.[Version],

car.[Publisher],

a1.[_ResourceGuid] [ResourceGuid]

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

WHERE IsManaged = '1' AND i.[Domain] LIKE 'Pluto' AND i.[Domain] LIKE 'Jupiter' AND car.[Name] NOT LIKE 'Security Update%' AND car.[Name] NOT LIKE 'Hotfix%' AND car.[name] NOT LIKE 'Update for%'

AND car.[name] NOT LIKE 'Altiris%' AND car.[name] NOT LIKE 'Microsoft .Net%' AND car.[name] NOT LIKE '%Service Pack%'

-----------------------------------------------------------------------------------------

Comments 2 CommentsJump to latest comment

mclemson's picture

I don't have access to a 6.x environment right now, but I would recommend cloning a 6.x report that has the collection picker that you need, then modifying it to add back in the data you need to report on and restrict with.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

KESH MAN's picture

Do you see something like the one I highlighted when you look at the query builder?

Altiris Administrator

Eli Lilly and Company, Indianapolis, USA

For Forum threads, please click "Mark as Solution" if answered