How to generate historical SLA/SLO data for dashboards from UIM DB
Article ID: 217237
Updated On:
Products
Issue/Introduction
Helpful queries are described below.
Environment
- DX UIM - Any version
- Backedn UIM database: MS SQL Server
Resolution
--Here are some SLA/SLO queries you might find useful. You'll have to adjust the date/time of course.
--A: Historical SLA reports
--Note: In all the queries provided below, change the parameter values as per your environment.
-- Identify the SLA by name - extract sla_id
SELECT * FROM S_SLA_DEFINITION WHERE name = 'Overall Compliance'
--B :Run the below query to get History SLA compliance for the sla
-- Inputs Needed - sla_id & period_begin
SELECT DISTINCT
s.name,
s.sla_id,
s.description,
s.period_code,
s.period_number,
d.percentage,
s.compliance_percentage,
s.compliance_warning,
s.compliance_warning_level,
d.period_begin,
d.period_end,
d.created,
t.zone_name,
t.zone_display,
t.zone_offset,
t.zone_offset_dst,
t.zone_display_token
FROM s_sla_definition s LEFT JOIN
s_time_zone t
ON s.time_zone_name = t.zone_name,
h_sla_compliance d
WHERE d.sla_id = s.sla_id
AND s.sla_id = 74
AND d.period_begin = '2025-01-01 00:00:00.0'
ORDER BY created DESC
--C: History SLO compliance
-- Inputs Needed - sla_id & period_begin
SELECT DISTINCT
s.slo_id,
s.sla_id,
s.name,
s.description,
s.weight,
d.percentage,
s.compliance_percentage,
s.compliance_alarm,
COUNT(n.slo_note_id) as noteCount
FROM h_slo_compliance d, s_slo_definition s
LEFT JOIN s_slo_notes n
ON s.slo_id = n.slo_id
AND n.note_date BETWEEN '2024-11-01 00:00:00.0' AND '2025-02-01 00:00:00.0'
WHERE d.slo_id = s.slo_id
AND d.created = (SELECT MAX(created) FROM h_slo_compliance WHERE slo_id = d.slo_id AND period_begin = '2024-11-01 00:00:00.0')
AND d.period_begin = '2024-11-30 00:00:00.0'
AND s.sla_id = 74
GROUP BY s.slo_id,
s.sla_id,
s.name,
s.description,
s.weight,
d.percentage,
s.compliance_percentage,
s.compliance_alarm
ORDER BY s.slo_id
--D: History QOS Constraints compliance
-- Inputs Needed - sla_id & period_begin
SELECT DISTINCT
s.sla_id,
s.slo_id,
sq.qos,
sq.source,
sq.target,
hqc.created,
hqc.percentage compliance_percentage
FROM h_qos_compliance hqc,
s_qos_constraints sq,
s_slo_definition s
WHERE
hqc.sla_id = s.sla_id AND hqc.slo_id = s.slo_id AND hqc.qos_const_id = sq.qos_const_id
AND sq.slo_id = s.slo_id
AND hqc.created = (SELECT MAX(created) FROM h_qos_compliance WHERE sla_id = hqc.sla_id and slo_id = hqc.slo_id and qos_const_id = hqc.qos_const_id AND period_begin = '2024-11-01 00:00:00.0')
AND hqc.period_begin = '2025-01-01 00:00:00.0'
AND s.sla_id = 74
ORDER BY s.slo_id
--*************************************************************
--E: Another alternate way is simply putting the SLA history information in an HTML5 dashboard by exploring the content of the following table which contains historic compliance:
select * from H_SLA_COMPLIANCE
Additional Information
--F: Basic queries to start with/find info
--List SLO compliance values for a specific SLA job
-- You must first check the sla_engine log for the given sla/job id values and replace them below.
DECLARE @job_id as int;
DECLARE @sla_id as int;
SET @job_id= 11042729;
SET @sla_id=283
SELECT AVG(percentage) AS pct FROM D_SLO_COMPLIANCE WHERE job_id=@job_id AND
sla_id=@sla_id
select * from S_SLA_DEFINITION;
select * from S_SLO_DEFINITION order by sla_id,slo_id;
select * from D_SLA_JOBS order by execute_date, sla_id;
select * from D_SLA_COMPLIANCE order by sla_id;
select * from D_SLO_COMPLIANCE order by sla_id, slo_id;
select * from D_QOS_COMPLIANCE order by sla_id, slo_id;
select * from H_SLA_COMPLIANCE order by sla_id;
select * from H_SLO_COMPLIANCE order by sla_id, slo_id;
select * from H_QOS_COMPLIANCE order by sla_id, slo_id, period_begin
Feedback
