Objective: Display trending SLA data in a dashboard. As of V8.4 there are several places where SLA data can be reported, but it is all for the current month or one month at a time. 


NOTE: This is as of Live Maps V8.4. There are planned updates to improve the SLA reporting withing the Live Maps Portal. 


The best way to accomplish this right now is via an SQL query widget in a Summary Dashboard. The following will assist you in creating the SQL query for your needs. This will require running queries using the SQL Server Management Studio with a user that has a minimum of "db_datareader" access to both the Operations Manger and DW databases. The individual SQL queries are attached to this document in a txt format. Please rename them to .sql. 


1. First open up the SQL Server Management Studio either on or connected to the server hosting your OperationsManager Database. 


2. Run the OpsMan_SLA_Query.sql targeted against the OperationsManager database. 


The results should look something like this. 




2. Now connect to your DW database via the SQL Management Studio. Open up the DW_SLA_Query.sql file. 


3. There are a couple settings that you may want to adjust in this query. First one is the number of months you would like to see. 


SET @num_historical_months = 4


4. Second is to add the SLA's you wish to show in the widget. 


-- ******************************************************************************************************************************
--  ENTER THE VALUES IN THE ORDER OF: [NAME YOU WANT TO GIVE TO THE SERVICE], [ServiceLevelObjectiveGuid], [ManagedEntityGuid]
-- ******************************************************************************************************************************

INSERT INTO #slas_to_report_on VALUES ('Policy Admin',                            'A12A405C-12E3-45A1-CCBE-017CBA75EE2A', '7A568B16-2DD8-1D4D-5D81-324E588520A3')
INSERT INTO #slas_to_report_on VALUES ('Infrastructure Components (Order Entry)', '886BBB4F-F57E-E0CC-2F82-04C11F016496', '62AFC7BB-FC02-BFD5-CE6A-99E450F2A80F')
INSERT INTO #slas_to_report_on VALUES ('Application Components (Amazon AWS)',     'FE262B07-68CD-5B1C-5C26-0EEC053FD8D4', 'F57F8F53-2397-64DA-072A-7AA16F5A1A88')



Here you will need to reference back to OpsMan_SLA_Query run in step 1. 


Find the SLA you wish to show and copy and paste the GUIDs into this section. The first GUID is the ServiceLevelObjectiveGuid. 




The first Value is the Display Name. This can be anything you wish. It does NOT need to match the value in the first SQL query. 


Once you have copied the sections over and it is complete, run the query and you should get a result that looks like the following. 




5. Next on to the Live Maps Portal. 


Open up the Summary Dashboard you wish to show the SLA information. 


Add a SQL Wiget, Give it a title and then copy and paste the (edited) DW_SLA_Query into the Query location. 



Click Save. 


Once you are done, it should look something like this.