Home » Orchestrator » Dashboard in SharePoint without any System Center database schema knowledge

Contoso.se

Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Microsoft infrastructure and system management. I am a principal engineer in the FastTrack for Azure team, part of Azure CXP, at Microsoft. Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.

Anders Bengtsson

MVP
MVP awarded 2007,2008,2009,2010

My Books
Service Manager Unleashed
Service Manager Unleashed
Orchestrator Unleashed
Orchestrator 2012 Unleashed
OMS
Inside the Microsoft Operations Management Suite

Contoso.se

Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Azure infrastructure and system management. I am a senior engineer in the FastTrack for Azure team, part of Azure Engineering, at Microsoft.  Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.



MVP awarded 2007,2008,2009,2010

My Books

Service Manager Unleashed


Orchestrator 2012 Unleashed


Inside the Microsoft Operations Management Suite

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 comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.