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 crq.collectionid=@CollectionID

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 crd.collectionid=@CollectionID

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 sw.collectionid=@CollectionID

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.

email

Written by , Posted .