United Kingdom Endpoint Management User Group

 View Only

Useful SQL queries concerning Collections/Filters 

Jan 15, 2014 02:27 PM

The following queries will allow you to see which collections/filters a resource belongs to, which resources are members of a collection/filter, which collections/filters are excluded from/included with a collection/filter, as well as which resources are excluded from/included with a collection/filter.

 

--/ Collections/Filters that a resource is a member of
SELECT vcoll.[Name] AS [Collection or Filter],vcoll.[Description]
FROM vCollection vcoll  
JOIN CollectionMembership cm ON cm.CollectionGuid = vcoll.[Guid]
JOIN vComputer vcomp ON vcomp.[Guid] = cm.ResourceGuid
WHERE vcomp.[Name] = 'name of resource'
ORDER BY vcoll.[Name] ASC

--/ Resources that are members of a Collection/Filter
SELECT vcomp.[Name] AS [Resource]
FROM vComputer vcomp   
JOIN CollectionMembership cm ON cm.ResourceGuid = vcomp.[Guid]
JOIN vCollection vcoll ON vcoll.[Guid] = cm.CollectionGuid
WHERE vcoll.[Name] = 'name of collection or filter'
ORDER BY vcomp.[Name] ASC

--/ Excluded collections
SELECT vcoll2.[Name] AS [Excluded Collection or Filter]
FROM vCollection vcoll2
JOIN CollectionExcludeCollection cec ON vcoll2.[Guid] = cec.SubCollectionGuid
JOIN vCollection vcoll1 ON vcoll1.[Guid] = cec.CollectionGuid
WHERE vcoll1.[Name] = 'name of collection or filter'
ORDER BY vcoll2.[Name] ASC

--/ Included collections
SELECT vcoll2.[Name] AS [Included Collection or Filter]
FROM vCollection vcoll2
JOIN CollectionIncludeCollection cic ON vcoll2.[Guid] = cic.SubCollectionGuid
JOIN vCollection vcoll1 ON vcoll1.[Guid] = cic.CollectionGuid
WHERE vcoll1.[Name] = 'name of collection or filter'
ORDER BY vcoll2.[Name] ASC

--/ Excluded resources
SELECT vcomp.[Name] AS [Excluded Resource]
FROM vComputer vcomp  
JOIN CollectionExcludeResource cer ON vcomp.[Guid] = cer.ResourceGuid
JOIN vCollection vcoll ON vcoll.[Guid] = cer.CollectionGuid
WHERE vcoll.[Name] = 'name of collection or filter'
ORDER BY vcomp.[Name] ASC

--/ Included resources
SELECT vcomp.[Name] AS [Included Resource]
FROM vComputer vcomp  
JOIN CollectionIncludeResource cir ON vcomp.[Guid] = cir.ResourceGuid
JOIN vCollection vcoll ON vcoll.[Guid] = cir.CollectionGuid
WHERE vcoll.[Name] = 'name of collection or filter'
ORDER BY vcomp.[Name] ASC

Statistics
0 Favorited
9 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

Aug 30, 2017 08:18 PM

Thanks a lot Joshua. :)

 It is showing policy and the target linked to it ...superb.

The only thing is it is not showing all the policies in the environment. It has pulled the data partially. Please guide me to pull all the policies created in the db.  

Aug 30, 2017 02:56 PM

I've just published the following that might be of use:
ServiceDesk - Software Request Process - Reports
https://www.symantec.com/connect/articles/servicedesk-software-request-process-reports
- Delivery Policies for Software Release
- Filters for Target
- Releases for Software Product
You could combine this sql with the above suggestions.

Aug 30, 2017 02:11 PM

im getting...

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ' '.
Msg 1087, Level 15, State 2, Line 15
Must declare the table variable "@PatchTargets".

and yes.. targeting my DB and running in SQL Studio... and i see the declare @PatchTargets so not sure why its burping

Aug 30, 2017 12:05 PM

I like to run the following SQL Query to find a list of policy targets; displays the Policy Name & Target:

--Run the following in MS SQL SMS against the Symantec_CMDB database:

declare @PatchTargetXML as table (Guid uniqueidentifier, [ResourceTarget] xml)
declare @PatchTargets as table (PolGuid Uniqueidentifier, TarGuid Uniqueidentifier)

insert into @PatchTargetXML
   SELECT i.Guid, [ResourceTarget] = CAST(i.State AS XML)
   FROM Item i
   JOIN ItemClass ic on ic.Guid = i.Guid  
   WHERE ic.ClassGuid = '49FE4304-E09D-4382-9026-715868FFB856'
  
insert into @PatchTargets  
   SELECT i.Guid, [ResourceTarget] = replace (replace (cast (tar.query('.') as nvarchar (100)), '<resourceTarget guid="', ''), '"/>', '')
   FROM @PatchTargetXML i  
cross apply [ResourceTarget].nodes('/item/resourceTargets/resourceTarget') as t2(tar)

select distinct
        i.Name 'Policy Name'
        ,replace (replace ((select Name from Item where Guid in (select cast (TarGuid as nvarchar (50)) from @PatchTargets where PolGuid = p.PolGuid) for xml path ('')), '<Name>', ''), '</Name>', ';     ') as 'Target' 
from @PatchTargets p
join Item i on i.Guid = p.PolGuid

 

--Hope this helps :)

Aug 30, 2017 11:31 AM

i dont know what table policies are in but here is for what filters are applied to a given machine. change my w10-bitlocker out as that is my test machine hostname..

maybe someone can help with the policy pary

Select t3.name as 'Hostname', t1.name as 'Filter', t1.Description, t1.CreatedBy, t1.CreatedDate, t1.ModifiedBy, t1.ModifiedDate  
FROM [vCollection] t1 inner join
[CollectionMembership] t2 on t1.GUID = t2.CollectionGUID inner join
[Inv_Aex_AC_Identification] t3 on t2.ResourceGUID = t3._resourceGUID
where t3.name in('w10-bitlocker')

Aug 30, 2017 08:22 AM

What is the end goal?

You can get the Target for a Policy then find out the Computers in the Target.

Locate a Target's guid so that you can then update its membership
https://www.symantec.com/connect/articles/locate-targets-guid-so-you-can-then-update-its-membership

Aug 30, 2017 06:45 AM

well i havent had any coffee yet... so just starting my day.. but policie, if im correct (prolly not )... will be advertisement or something.. ive got some scripts .. let me see what i can find.. then i also cloned one the other day and it had filter in it.. which i didnt need but still...

 

Aug 29, 2017 08:46 PM

Please help me out with sql query to search for a particular filter applied to which policies.

Aug 04, 2015 01:08 PM

This will get you the maintenance window for a resource

declare @resGuid uniqueidentifier

set @resGuid = 'cbac740a-44fd-4f13-9e39-809fc15d6102' --Change Me

select distinct i.Guid,
	i.name as 'MaintenanceWindowGuid'
from vMaintenanceWindows i
inner join ItemAppliesTo ir
	on ir.ItemGuid = i.Guid
inner join ResourceTargetMembershipCache cm
	on cm.ResourceTargetGuid = ir.ResourceTargetGuid
left join ItemActive ia
	on i.Guid = ia.Guid
where cm.ResourceGuid = @resGuid
	and isnull(ia.Enabled, 1) = 1

You can then use the guid returned by above to see if the policy is active:

select *
from [ItemActive]
where [Guid] = 'B7C6C28A-1978-4A1C-BBC0-CC8B575D7CDA' --GUID from previous query results

...or you can get really creative and probably join the 2 queries.

Jul 31, 2015 03:46 AM

All All,

Could you please help me with a query to know the status of 'Maintenance Window' in ITMS 7.6 HF 2 ?

Thanks in advance.

 

Nov 06, 2014 03:01 AM

Hi brockrb

Why don't you just use the first query:

--/ Collections/Filters that a resource is a member of
SELECT vcoll.[Name] AS [Collection or Filter],vcoll.[Description]
FROM vCollection vcoll  
JOIN CollectionMembership cm ON cm.CollectionGuid = vcoll.[Guid]
JOIN vComputer vcomp ON vcomp.[Guid] = cm.ResourceGuid
WHERE vcomp.[Name] = 'name of resource'
ORDER BY vcoll.[Name] ASC

and add a report parameter to it?

 

You could also just use the Resource Manager of a Client and check out the "Filter Overview". Or "Policy Overview".

Nov 04, 2014 09:42 AM

Awesome queries, thank you.

We target comptuers with software policies based off of filter membership to prevent tier 1 techs from accidentally targeting the whole environment.  I was looking for a way to find out which filters a comptuer is a member of because when someone leaves the company their licensed software has to be removed.  That means we have to remove their comptuer from the fitlers so that they don't get the software again.  Do you (or anyone else) know how to convert Collections/Filters that a resource is a member of to a report in the SMC where the computer name is a parameter that can be set?  I would like our tier 1 techs to be able to run the report to see which fitlers a computer has to be removed from.

Apr 02, 2014 08:40 PM

I posted a IE Inventory Data Class article and it has the SQL I use for dynamic to target a filter specifically... then either a IN or NOT IN for anything else.. usually I use it for software... but can be used for anything...

works great. I took it from where I used it in NS6 to do dynamic collections. May not be the most efficient but works really well...

select Guid from vResource where ResourceTypeGuid in 
(select ResourceTypeGuid from ResourceTypeHierarchy 
where BaseResourceTypeGuid='493435f7-3b17-4c4c-b07f-c23e7ab7781f')
and GUID IN
(
Select t2.[ResourceGUID]
FROM [vCollection] t1 inner join
[CollectionMembership] t2 on t1.GUID = t2.CollectionGUID inner join
[Inv_Aex_AC_Identification] t3 on t2.ResourceGUID = t3._resourceGUID
WHERE t1.[Name] = 'Windows XP Computers'
and t2.[ResourceGUID] NOT IN 
( 
select t1._ResourceGuid
from [Inv_AddRemoveProgram] t1
where t1.DisplayName like '%(KB2718523)%'))

https://www-secure.symantec.com/connect/downloads/custom-inventory-ie-version

Apr 02, 2014 03:41 PM

Here's another SQL snippit I use regularly to append to any SQL query where you want to limit the results to a specific filter by name (instead of by filter guid which can be a pain).  In this case vc.[Guid] refers to vComputer but you can use this to limit a query for any computer list containing the guid.

...

WHERE VC.[Guid] in
(
 SELECT distinct cm.[ResourceGuid]
 FROM vCollection vColl
 JOIN CollectionMembership cm ON vColl.[Guid] = cm.[CollectionGuid]
 WHERE vColl.Name = 'YourFilterNameHere'
)

Apr 02, 2014 03:01 PM

i will have to add these to my saved useful queries.. I have a mess that i use over and over just slightly modify at needs.. from dynamic filters for sw deployment to hw and all else...

Jan 17, 2014 06:45 AM

Thanks a lot, very handy and useful. 

Jan 17, 2014 03:15 AM

I am glad that you like them.  Yes they will work on both versions.

Jan 17, 2014 03:07 AM

Hi, SK:

You are my heroe :)

Thanks for providing us with these usefull queries.

One question related to it: Are these queries compatible to 7.1 and 7.5?

Related Entries and Links

No Related Resource entered.