Dashboard in SharePoint without any System Center database schema knowledge

 

Some time ago I was working in a project were we needed a dashboard to display data from multiple System Center components. You can often find a dashboard to download with pre-populated SQL queries, but it works for one component at a time. We needed one dashboard to work with multiple components and different databases, and we didn’t wanted to write any SQL queries against the System Center databases. Writing SQL queries to get the right data from the System Center databases can be a very time consuming work. The solution was to use the “old classic” Configuration Manager Dashboard that I have blogged about before, here and here, together with Orchestrator as a connector. We also created a small database in the middle. The dashboard database.

 

 

A number of runbooks in Orchestrator gets data from System Center components and from a custom database. The result is written to the dashboard database. The SharePoint dashboard queries the dashboard database. We created a set of tables in the dashboard database, for example one table for incidents, one for service requests, one for VM and one for Operations Manager alerts. The table for Service Manager had cells to store information like number of total number of open incidents, closed incidents today, new incidents for today and number of incidents with top severity and priority. Each cell in the table was updated by separate runbooks in Orchestrator, as not all data needed to be updated on the same frequency.

The runbook in figure 1 show a runbook that gets number of open alerts in Operations Manager and writes that to the dashboard database. As you can see we use a counter to count number of alerts. The Get Alert activity don’t publish a “number of object” value, so instead we have to do the counting in the runbook.

Figure 1

Figure 2 show a runbook that gets number of incidents created today in Service Manager and writes that to the dashboard database. The Get Object activity publish a Number of Objects value that we write to the database, then we don’t need to use counters as in Figure 1. To avoid running the Write To Database activity multiple times the Get Open Incidents Created Today activity is configured to flatten the result.

A nice benefit with using runbooks to get the data from the source system is that we don’t need to know anything about the source system database structure. We use only default activities from different System Center integration packs and Orchestrator standard activities.

Figure 2

Figure 3 show an example of the dashboard. There is a number of charts, gauges and tables you can use to present the data. Figure 4 show the configuration for one of the gauges. As we use a custom dashboard database with a very simple structure each SQL query is short and simple.

Figure 3

Figure 4

Note that this is provided “AS-IS” with no warranties at all. This is not a production ready management pack or solution, just an idea and an example.

Leave a Reply

Your email address will not be published. Required fields are marked *