Introduction

In the following article, we will describe a much more efficient way of showing the usage statistics of our SQL Server Reporting Services hosted reports.

References

Most of the queries below have been addressed in another article, Obsoleting Unused SSRS Reports. Even though they are really useful, the article shows the queries through SQL Server Management Studio.

The Problem

One of the problems that often occurs in our organization, as well as in some of our customers', is the challenge to get immediate feedback about the usage statistics for reports. Usually, the requests for creating reports are out of control, and some of the reports have been used "that one time" and not anymore. In the worst-case scenario, many reports aren't ever executed and some of them could become even have overlap or be duplicated.

Therefore, it is important to know those usage statistics, user by user, and report by report. This is to make the reader aware of them, let him interpret the values in multiple ways and various graphical layouts. While this is not possible with a tabular format (unless you export the values using external tools such as Excel), it is simple with the approach described below.

Our Solution: Grafana

We immediately considered two factors: simplicity and efficiency, in order to make this first-sight dashboard. Grafana enables us to meet both of them, as well as being very powerful. Even though this is not the right definition for it, we can say that it is a portal to create dashboards using connectors, which support the most famous tools that return data. We can find connectors on its marketplace. For instance, tools such as PRTG and Prometheus (monitoring), NewRelic (APM), pure SQL and NoSQL data sources are supported:

Obviously, we can find SQL Server. Also, we can contribute to create others, as well as to modify Grafana itself, since it is an Open Source project. Examples of possible graphical representations are listed below:

In order to create the dashboard, simply add each panel with the appropriate button.

Then, write the query and modify settings to get the desired type of representation.

As mentioned before, the connectors are numerous. Once selected, you can configure them with parameters:

If you would like to install and configure Grafana, you can read the official documentation, which also includes a short guide that illustrates how to take your first steps.

Conclusions

With half a day of work (including the setup of the server), we have solved one of the most important problems of our customers, derived from the lack of awareness of reports deployed in production environments. We did it with a very little effort and the result, as you can see, is pleasant and effective. Everything is now ready to be published every time we update the dashboards also through a delivery software (Octopus Deploy, Jenkins or Azure DevOps), so all these things fall into the second and third way of DevOps (according to The Phoenix Project): Immediate Feedback and Continuous Improvement.