In SCCM 2007 we had many custom reports that were needed by different people. Often it was the same report, with just one parameter differing, so it was displaying information user needed, but nothing more. For example administrators in different countries were only able to see reports about computers in countries they were responsible for, but not others.
Now in our company this quickly meant over 200 reports. in fact it was just 8 or 10 reports, differing in names, one filter value and permissions on them.
With upgrade to SCCM 2012 i thought it would be a good time to update our reports as well. And since SCCM 2012 enables us to use RBAC, I gave it a go. Firstly you have to set permissions for users. I will explain how to do this in another post. For now let’s just say that users have permissions correctly assigned to them.
If you are creating new report, rather than reusing old ones, you can skip first couple of steps and just open Report Builder.
Now we go and open our report in Report Builder.
Open SCCM Management Console, go to Monitoring and click on Reporting. From there Click on the link for Report Manager.
From the Report manager site select the report you want to edit, click on down arrow on the right of the name and select Edit in Report Builder.
Now you have the report opened in Report Builder. If you are going to create a brand new report, you need to read from here on. 🙂
User you are using for reporting, which was defined during the installation is a member of smsschm_users database role. This role has SELECT permissions on several views and functions. For now we are interested in functions starting with fn_RBAC_* these functions are used for RBAC reporting. Their names directly mirror view name they “use”. So if you used v_R_system, to get computer name, so SELECT Name0 from v_R_system, you can use select name0 from fn_rbac_R_system(@UserSIDs) to get the same result, but using RBAC! For testing, you can replace @UserSIDs with ‘disabled’, so you get the same result as without the use of RBAC. For testing RBAC, see this great post:
So now we have query written using only functions, no more views, but it is not working yet. We still have some work to do.
First, we need to create additional Dataset.
Right click on Datasets.
Name it UserTokens, select Use a dataset embedded in my report and paste the following as Query:
select dbo.fn_rbac_GetAdminIDsfromUserSIDs(@UserTokenSIDs) as UserSIDs
This will create additional parameter @UserTokenSIDs in your report. You now at least 2 parameters. Order them so @UserTokenSIDs is the top one, and @UserSIDs is the second from the top.
Now open @UserTokenSIDs properties and on General tab select Parameter visibility: Internal. On Default Values tab select Specify values -> Add -> Formula and paste the following and save.
Open @UserSIDs properties and on General tab select Parameter visibility: Hidden. On Default Values select Get values from a query and select Dataset we created previously, UserTokens, and for Value field, select UserSIDs. If you named dataset and/or Value differently, adjust here accordingly.
Now we just have to do one last thing before saving, set References. On Report Properties open Assemblies and add a value for References:
I hope this picture will explain in enough details 🙂
Now do not forget to save and test if it works as expected.
Do not forget to replace ‘disabled’ with @UserSIDs in the final report! 🙂