SCCM Report: Users in a User Collection

This may seem a little redundant, but I had a need to create a list of users and their information from a User Collection in SCCM. This report pulls that information.

Create a Report in the Users folder called "All Users in a User Collection", and create 3 datasets

Dataset 1: CollectionNames

This query pulls the names of all the user collections, and feeds the ID into the @ID parameter, that you will create

select CollectionID, Name
from dbo.v_Collection C
Where
C.CollectionType = 1
ORDER BY Name

Create the @ID parameter

General
- Name: Collection Name
- Prompt: ID
Available Values
- Get values from a query
- Dataset: CollectionNames
- Value Field: CollectionID
- Label field: Name
Default Values
- Get values from a query
- Dataset: CollectionNames
- Value Field: CollectionID

Dataset 2: CollectionNameSelected

This query will get the name of the variable you selected when you run the query. It's used in the report table to make it look nice

select Name
from dbo.v_Collection C
Where CollectionID = @ID

DataSet 3: MainQuery

This is the main query that pulls the results you want from the selected user collection

select
U.Full_User_Name0 as [User],
U.User_Name0 as [User Name],
U.User_Principal_Name0 as [UPN],
U.department0 as [Department]
from
dbo.v_Collection C
join dbo.v_FullCollectionMembership FCM  on C.CollectionID = FCM.CollectionID
join dbo.v_R_User U on U.Name0 = FCM.Name
Where
FCM.CollectionID = @ID
Order By
U.Full_User_Name0

Create table with the results.

The top cell uses this expression to title the report:
="List of users in group: " & First(Fields!Name.Value, "CollectionNameSelected")

The right expression is the title of the User Name column and the count of users returned by the query
="User Name (" & Count(Fields!User.Value) & ")"

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.