SCOM Dashboards - Part 6 - Adding SQL Queries to the Dashboards

Before reading this post, please ensure you have reviewed my previous posts on SCOM Dashboarding from the links below:

SCOM Dashboards - The Intro
SCOM Dashboards - Part 1 - Installing WSS 3.0
SCOM Dashboards - Part 2 - Installing the Dashboard Solution Accelerator
SCOM Dashboards - Part 3 - Customizing the Dashboard Banner and Logo
SCOM Dashboards - Part 4 - Customizing the Dashboard Tabs
SCOM Dashboards - Part 5 - Installing the Service Level Dashboard (SLD 2.0)

To customise your SCOM Dashboard and remove the pre-inserted SCSM web parts, follow the screens below:

Open your SCOM Dashboard and click on 'Edit Page' from the menu on the right hand side

Input in a new Dataset Configuration Name, add your SQL Server and instance name, add your OperationsManager database name and copy and paste your SQL query into the SQL Query window as below (SQL queries can be obtained from the list of community blog references in the final part of this series)

Select 'Parse Query' and then 'Validate Query' and ensure you get the green tick and preview data before continuing

Expand 'Charts', leave 'Pie' selected and then click 'Add' to create a Pie Chart with your SQL query information. Fill out the fields similar to the graphic below

Once this is completed, click on 'Save and Close' to complete the new SQL query injection

Now what you need to do is to add your new 'Top20AlertsbyRepeatCount.xml' file into an existing Web Part for display within your dashboard

Click on an the 'edit' button from within an existing web part on the dashboard and select 'Modify Shared Web Part'

This will open the web part for editing on the right hand side of the screen. From here, use the 'Select Data Set Configuration' button and highlight your previously created SQL query as below

 
Next, put an entry into the 'Title' field to give your new web part a name and then leave all of the other settings as they are and click on 'OK'

 
This should now replace the old SCSM web part on the dashboard with your new 'Top 20 Alerts by Repeat Count' dashboard as shown below

Repeat this process as many times as you like using different SQL queries and chart types and then changing the existing SCSM web parts one by one until you get the dashboard that you want for your business

If you want to remove any excess web parts and tidy up the dashboard a bit, then all you need to do is to select the 'X' in the corner beside the 'Edit' button to remove it completely

Your final SCOM Dashboard tab should look something like this:

With the SCOM dashboard itself finished now, in Part 7 I will describe how to create a scorecard web part that acts as a Distributed Application Health State dashboard.