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],
cdepend.SourceCollectionID as 'Collection Dependency',
cc.Name as 'Collection Dependency Name',
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.
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.
- Created on .