How to create an RBA capable report for ConfigMgr R2

Hello all, ConfigNinja here trying to show you a few tips and tricks to convert your current custom reports using the new RBA(Role Based Administration) for reports. https://technet.microsoft.com/en-us/library/dn236351.aspx#BKMK_WhatsNew_Monitoring_and_Reporting This new feature on R2 brings  lots of benefits, especially for those users that don’t need to have access to all the data within ConfigMgr and limit what you allow them to view. Read more about this here, https://technet.microsoft.com/en-us/library/7ca322fc-bbbf-42c8-82c9-6fc8941ef2e6#BKMK_RoleBaseAdministration.

STB13_Csonger_03

Modify a custom report

We are going to modify your custom report using Report Builder, Once you have the Report Builder open we will review a few things before updating your new custom report.

First let’s review one of the new reports from ConfigMgr 2012 R2, at your SSRS Website https://cmsite/reports.

Next, let’s go to Software Distribution - Collections > All resources in a specific collection click on the drop down and select edit in Report Builder.

Now that you are on Report Builder, let’s review a few details that are new on this report.

clip_image002

We have UserTokenSIDs and UserSIDS on the Parameters section; also we have a new Dataset call DataSetAdminID.

Let’s review the query inside the DataSetAdminID:

 “select dbo.fn_rbac_GetAdminIDsfromUserSIDs (@UserTokenSIDs) as UserSIDs”

Now let’s review the 2 prompts that are part of my report.

@UserTokenSIDs general Configuration:

clip_image004

Available Values leave as default that should be NONE.

clip_image006

Default Values, Specify values.

The value will be an expression, click on the expression button to see the expression code.

 =SrsResources.UserIdentity.GetUserSIDs(User!UserID)

Leave this as the default.

Let’s review @UserSIDs Prompt

clip_image008

On the Available Values page, leave as default wich should be NONE

Review the Default Values page and other pages but do not make changes.

clip_image010

Now that we understand the prompt let’s review the main Datasets of this reports and review the code.

On the DataSet1, the code is as follow.

 “Select Distinct CollectionID, Name FROM fn_rbac_collection(@UserSIDs) order by Name”

This T-SQL Code in the past use to be like this:

 “Select Distinct CollectionID, Name FROM v_Collection Order By Name”

We had replaced the view for a function, and with the replace of a V_ we need to add fn_rbac_ and then after the function or view add (@UserSIDs).

Let’s take a look at the Dataset2, here is the query inside this dataset.

      

 “Select fcm.Name,
 
 CASE WHEN coll.CollectionType=1 THEN fcm.SMSID ELSE fcm.Name END as Is,
 
 CASE WHEN ResourceType = 3 THEN ‘*’ ELSE ‘’ END as C066,
 
 CASE WHEN ResourceType = 4 THEN ‘*’ ELSE ‘’ END as C067,
 
 CASE WHEN ResourceType = 5 THEN ‘*’ ELSE ‘’ END as C068,
 
 SiteCode,
 
 CASE IsDirect
 
 When 1 THEN ‘*’
 
 When 0 THEN ‘’
 
 END AS C069,
 
 Coll.CollectionType
 
 From fn_rbac_FullCollectionMembership (@UserSIDs) fcm
 
 INNER JOIN fn_rbac_collection(@UserSIDs) coll on fcm.CollectionID = coll.CollectionID
 
 Where fcm.CollectionID = @ID”

 

As you can see on this query, the query had been modified to first validate the UserSIDs before displaying the report data.

Now let’s convert a custom report without Role Based Administration into using RBA.

For this, I will modify a Quick Fix Engineering Report that was created without RBA.

clip_image012

As you can see on this image, there is a missing Prompt and a missing data set from this report.

Plus let’s take a look at how each dataset query is writing.

DataSet1

      

 “Select
 
 QFE.HotFixID0
 
 From
 
 V_GS_Quick_Fix_Engineering QFE
 
 Order By QFE.HotFixID0”
 
  As you can see on this query, we are using the View and not the RBA Function.
DataSet2
 
 “Select
 
 QFE.ResourceID
 
 ,sys.Netbios_name0
 
 ,QFE.Caption0 as ‘URL Info’
 
 ,QFE.Description0 as ‘Classification’
 
 ,QFE.HotFixID0 as ‘KB Article’
 
 ,QFE.InstalledBy0 as ‘Installed By’
 
 ,QFE.InstalledOn0 as ‘Installed Date’
 
 FROM
 
 V_GS_Quick_Fix_Engineering QFE
 
 LEFT Join v_r_system sys on qfe.resourceid = sys.resourceid
 
 Where qfe.HotFixID0 like @HotFix”

 

Now let’s start by adding the DataSetAdminID to the Report.

To do this go to Datasets, right click Add DataSet

In the Name Field enter: DataSetAdminID

Click Use a dataset embedded in my report:

Select the Data Source: Should be the ConfigMgr Default data source or a custom one.

In the Query enter the following query statement:

 select dbo.fn_rbac_GetAdminIDsfromUserSIDs (@UserTokenSIDs) as UserSIDs

clip_image014

Click OK to finish the new dataset.

clip_image016

The dataset has been added and also a new prompt.

Let’s now modify the query on Dataset1 and Dataset2 to use the RBAC function.

clip_image018

Dataset1 has been changed; all I did was replace V_ to fn_rbac and add the (@UserSIDs).

clip_image020

Here is the updated DataSet2.

On the parameters, move the UserTokenSIDs as the first priority.

clip_image022

And that’s all you will need to do to convert your custom report from non RBA to use RBA Function for reports, I hope these instructions were helpful and you can create your new reports ready for RBA.

as a bonus for reading this blog post I have uploaded the template reports I used for this blog post here:

https://gallery.technet.microsoft.com/Quick-Fix-Engineering-0e9fb49c

Does this information help you?

Happy Thanksgiving!!

Santos Martinez – Sr. PFE – ConfigMgr and Databases

Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of any included script samples are subject to the terms specified in the Terms of Use