Basically, I just want a list of all software policies assigned to a filter.

You can do this manually the long way by running these four queries:

USE Symantec_CMDB 

 
-- 1) Get the guid of the filter we are looking at
 
SELECT  vi.[Guid]
 
FROM vItem vi
 
WHERE vi.Name = 'YOUR FILTER NAME'
 
-- 2) Get the ParentItemGuid from vItemReference using the resolved Guid
 
SELECT vir.[ParentItemGuid]
 
FROM vItemReference vir
 
WHERE vir.[ChildItemGuid] = '77210836-CD27-4681-B089-A26FCD7A6356' -- Software X Filter Guid
 
-- 3) Get the PolicyGuid from vPolicyAppliesToResource using the resolved ParentItemGuid
 
SELECT DISTINCT vp.[PolicyGuid] 
 
FROM vPolicyAppliesToResource vp
 
WHERE vp.[ResourceTargetGuid] = 'AC1A648A-E729-420A-8C26-0005D78CA4B9'
 
-- 4) Get the name of the resource using the resolved PolicyGuid
 
SELECT vi.[Name] 
 
FROM vItem vi 
WHERE vi.[Guid] = 'C4C3A54E-82AB-4BB9-A306-886C9F541885'

I'm no SQL genius so surely there is a better way to do this.

Any help would be very much appreciated.

Cheers,

Rhys

Hey Rhys,

Give this a try.  It may not be the most efficient way to do this but it works.  I had to do something similar for a workflow I did.  I baiscally started with the step 4 query and worked my way back to step 1 replacing the Guid with the sql query in the previous step.  Let me know if it works out.

Good Luck!

SELECT vi.[Name]

 FROM vItem vi

WHERE vi.[Guid] = (SELECT DISTINCT vp.[PolicyGuid]

 FROM vPolicyAppliesToResource vp

 WHERE vp.[ResourceTargetGuid] = (SELECT vir.[ParentItemGuid]

 FROM vItemReference vir

 WHERE vir.[ChildItemGuid] = (SELECT vi.[Guid]

 FROM vItem vi

 WHERE vi.Name = 'YOUR FILTER NAME')))

That works really well.

Only problem is if there is more than one software resource assigned to the filter, I get the following error:

 Msg 512, Level 16, State 1, Line 3

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.