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
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.
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
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 :)
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')
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
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...
Please help me out with sql query to search for a particular filter applied to which policies.
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.
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.
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".
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.
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
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' )
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...
Thanks a lot, very handy and useful.
I am glad that you like them. Yes they will work on both versions.
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?