Endpoint Insights
How to Create a Collection Prompt Query
Topics: Endpoint Insights
This is one of the most requested asks in the forums, “How can I add a collection to my report?” After a bit of toing and froing, what the person is really asking for is how to add a collection prompt to their report. Before adding the prompt, however, the first step is to include a collection prompt query in your report. A collection prompt must populate the collection variable. This blog post shows you what needs to change within a query in order to add a collection prompt.
Queries
Before I dive into the details, the screenshot below shows you what I ultimately want to see. SCCM uses collections to group computers/users together. It therefore makes sense that admins would want to see reports that limit results based on a collection. It is for this reason that all of Enhansoft’s dashboards and reports within Enhansoft Reporting (ER) and Warranty Information Reporting (WIR) include a collection prompt. As an example, you can see on our KB site (ER and WIR) that we limit our reports to a collection called Oxford Regional Office. If we didn’t do that, you would see results for the whole of our SCCM environment.
Next, are the sample queries that I’m going to use in my example.
List of Computers for a System Role (Main)
This sample query displays the computer name, user name, OS name and system role (Server or Workstation).
Select
RV.Netbios_Name0 as ‘Computer’,
RV.User_Name0 as ‘User’,
OS.Caption0 as ‘OS’,
S.SystemRole0 as ‘System Role’
From
dbo.v_R_System_Valid RV
Inner Join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
Inner Join dbo.v_GS_SYSTEM S on RV.ResourceID = S.ResourceID
Order by
RV.Netbios_Name0
List of Collection Prompts Query
This query displays all available collections.
Select
Coll.Name as ‘Name’,
Coll.CollectionID
from
dbo.v_Collection Coll
Order by
Coll.Name
Creating a Collection Prompt Query
How do you add a collection to a SQL Server query? Well, the answer is to add the SQL Server view called dbo.v_FullCollectionMembership to the query. Next, you limit the results to the CollectionID. The CollectionID comes from the collection prompt query.
The v_FullCollectionMembership view lists all computers (ResourceID) and what collection (CollectionID) they belong to. Therefore, if you know the CollectionID you can limit the report to display results based on that collection. You can get the CollectionID and collection name from the v_Collection view. I will use that view within this prompt query. It isn’t that hard. I am simply adding three lines to the sample query I showed you earlier.
From Section
Inner Join dbo.v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
Where Section
Where
FCM.CollectionID = @CollID
Updated Query
The final, updated query looks like this:
Select
RV.Netbios_Name0 as ‘Computer’,
RV.User_Name0 as ‘User’,
OS.Caption0 as ‘OS’,
S.SystemRole0 as ‘System Role’
From
dbo.v_R_System_Valid RV
Inner Join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
Inner Join dbo.v_GS_SYSTEM S on RV.ResourceID = S.ResourceID
Inner Join dbo.v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
Where
FCM.CollectionID = @CollID
Order by
RV.Netbios_Name0
Now, all you need to do is update your report and then you can go ahead and add the collection prompt.
Tips
Once you master how to create a collection prompt query, take a look at these blog posts for more helpful tips on how to make your reports even better.
Adding a Prompt Value to the Title of a SCCM Report
How to Create a Date Prompt in SSRS – If you are unsure about the steps for adding a prompt to a report, my colleague, Leonard Suares, covers the basic steps within this blog post.
Using a collection to limit the results of your report makes it more helpful and user-friendly. If you have any questions, please feel free to touch base with me @GarthMJ.
