ConfigMgr 2012 SQL Report with Collection information about Include or Exclude other collections

Either my web searching skills have left me; or no one else has had occasion to create this type of report for SRS; but I couldn't find a SQL query I could use in SRS to show me collection details about when a particular collection was "including" another collection, or "excluding" another collection.  Here's what I ended up with; perhaps there is an easier or better way, but this worked:

select distinct c.name as [Collection Name],
c.collectionid,
cdepend.SourceCollectionID as 'Collection Dependency',
cc.Name as 'Collection Dependency Name',
Case When
cdepend.relationshiptype = 1 then 'Limited To ' + cc.name + ' (' + cdepend.SourceCollectionID + ')'
when cdepend.relationshiptype = 2 then 'Include '  + cc.name + ' (' + cdepend.SourceCollectionID + ')'
when cdepend.relationshiptype = 3 then 'Exclude '  + cc.name + ' (' + cdepend.SourceCollectionID + ')'
end as 'Type of Relationship'
from v_Collection c
join vSMS_CollectionDependencies cdepend on cdepend.DependentCollectionID=c.CollectionID
join v_Collection cc on cc.CollectionID=cdepend.SourceCollectionID
where c.CollectionID = @CollectionID

and where, of course, you then (in Report Builder) have another query just for use by the parameter "CollectionID": select c.collectionid, c.name from v_collection c order by c.name

With that, Report Builder 3.0 to publish it into SRS, and then you can then pick a collection by name, and see what types of relationships it has with other collections.  In this example, the collection called "Sample Collection for the Blog", happens to have 3 relationships to other collections.

CollectionRelationshipReport

Of course, you can also get more information about your collections; like...collection queries for that collection

select crq.name, crq.queryexpression from v_collectionrulequery crq where [email protected]

or... is that a collection which has direct members and no queries; like...

select count(*) as 'Number of Direct Member Rules' from v_collectionRuleDirect crd where [email protected]

or...are there any service windows applied to that collection:

select sw.Name, sw.Description, sw.Duration, sw.IsEnabled, sw.ServiceWindowID from v_ServiceWindow sw where [email protected]

There's also v_CollectionSettings and v_CollectionVariable which might be interesting.  So you can make up a "everything you wanted to know about this collectionid" report if you so desire.  Just need to be creative with Report Builder and having multiple tables in Report Builder.

CMCB

  • Created on .
Copyright © 2019 - The Twin Cities Systems Management User Group