Endpoint Insights
Seven Tips for Writing SCCM Dashboards
Topics: Endpoint Insights
Seven Tips for Writing SCCM Dashboards
At the last Midwest Management Summit which was held at the Mall of America, I was stopped in the hallway after one of my SCCM reporting presentations. I was asked for some additional report writing tips that I could share with SCCM administrators, so I decided to write this blog post about seven tips for writing SCCM dashboards.
Tip 1: Writing SCCM Dashboards
Always use a development environment to test your SQL Server queries
In the above screenshot you can see the execution time and the number of results for the same query. The first one (less than 1 second) is for a correctly written query, and the second one (9 seconds) is for a poorly written query. My lab of only 35 computers is very tiny, but imagine the time difference if you had an environment with 10,000 computers!
Catching this problem early on in a development environment reduced its negative impact, so take my advice and test your SQL Server queries for SSRS or Power BI reports/dashboards in a development environment.
What if you don’t have a full SCCM development environment? One solution is to install SQL Server with a copy of the SCCM database and SSRS on your computer. When you do that, please don’t forget to enable the CLR option. In order to install SSRS on you laptop, please see How Do You Install SQL Server Data Tools?
Tip 2: Writing SCCM Dashboards
Start by creating your SQL Server queries in SQL Server Management Studio (SSMS)
Unlike SSMS, when you use Report Builder (RB) or Visual Studio 2017/2015, the interface does not provide you with all of the options you need to thoroughly test your SQL Server query. Without these options it is difficult not only to create, but to troubleshoot, or more importantly, to check the performance of your SQL Server query.
Take the poorly written query that I mentioned in the previous tip, running it on a production SQL Server would effect my SCCM environment. Creating this query in SSMS helped me because I could tell there was a performance issue right away.
Tip 3: Writing SCCM Dashboards
Always use a development environment to create and test your SCCM SSRS or Power BI dashboards
Similar to Tip #1, creating and testing your reports in a production environment is frowned upon because this can have a negative impact on the SQL Server. Even when you test each individual query with SSMS, it doesn’t mean that when all of the queries are combined into one dashboard that you won’t have issues. In addition to SQL Server query performance issues, report processing can also be a factor that will negatively affect SQL Server performance. For example, processes such as filtering, sorting, etc., or simply the volume of results could consume too much network bandwidth.
If you don’t have a full SCCM development environment, you can install SQL Server with a copy of the SCCM database and SSRS on your laptop. To install SSRS on you laptop, please see How Do You Install SQL Server Data Tools? When you install SQL Server with a copy of the SCCM database on your laptop, please don’t forget to enable the CLR option.
Tip 4: Writing SCCM Dashboards
Set the Visual Studio-BI (VS-BI) report project properties to match the appropriate SQL Server version
SQL Server Reporting Services (SSRS) has been around for a long time and there are several versions of SSRS that will work with various versions of SQL Server. The SCCM product team still fully supports most of these SSRS versions, but this also means that there are several RDL versions to match the SSRS versions. If the correct version of SQL Server is not set when using RB or VS-BI, you will get an error when you try to publish (or upload) the report.
Tip 5: Writing SCCM Dashboards
Only use supported SQL Server views and functions
Microsoft ONLY supports querying the SCCM SQL Server views and SCCM SQL Server table-value functions. However, NOT all SQL Server views or functions are supported.
Only SQL Server views and functions that have smsschm_users “Select” or “Execute” permissions are supported with reporting whether you’re using Power BI, SSRS or any third-party tools. The simplest way to grant these permissions is to leverage the SCCM Report Editor security role (see the next tip!). Instructions about how to do this are also found in the Granting SQL Server Security Rights section of this post, How to Start Editing SCCM Reports with Report Builder.
Tip 6: Writing SCCM Dashboards
Create a SCCM Report Editor security role
If you don’t need any permissions within SCCM to create a report from scratch then why would you need to create a security role? I recommend creating a security role in order to document and grant access to those that can create or edit reports/dashboards using the SCCM database. This works for Report Builder, Power BI, SQL Server Data Tools – Business Intelligence (SSDT-BI) (sometimes called Visual Studio-BI (VS-BI) or any third-party tools. Also, if you plan to use the, “Click-to-Run,” version of Report Builder, it will require security role permissions.
See this post for more information about the SCCM security role, How to Start Editing SCCM Reports with Report Builder.
Tip 7: Writing SCCM Dashboards
Create a SCCM Report Reader security role
Everyone knows that with the appropriate rights, anyone can view SCCM dashboards or reports from within the SCCM console. However, did you know that there’s an easier way? You can also view SCCM dashboards and reports from the SSRS website! Viewing reports from the SSRS website doesn’t require as many steps to grant permission. All you need to do is setup a SCCM Report Reader security role. So, for those of your colleagues that don’t need to view reports through the SCCM console, you now know there’s a much simpler solution.
These two posts will guide you through the process: New Tool for Creating Configuration Manager 2012 Report Reader AD Security Groups and New Report Reader Tool for Configuration Manager.
Conclusion: Seven Tips for Writing SCCM Dashboards
If you have any questions about these tips for writing SCCM dashboards, please feel free to contact me @GarthMJ or reach out to Recast Software here.
