Video Screencast Help
Scheduled Maintenance: Symantec Connect is scheduled to be down Saturday, April 19 from 10am to 2pm Pacific Standard Time (GMT: 5pm to 9pm) for server migration and upgrade.
Please accept our apologies in advance for any inconvenience this might cause.

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