Moving maintenance windows to SLO exclusion periods

Document created by jonhcw on May 4, 2015Last modified by SamCreek on Dec 17, 2016
Version 3Show Document
  • View in full screen mode

I'm currently working on automating SLA operations and one thing I want to do is to automatically create SLA/SLO exclusion periods based on maintenance mode entries created with the new maintenance mode functionality in USM. I have a probe to automate creating SLAs etc so my initial plan was to build this functionality into that probe. However it turned out that this is not too difficult to do purely in SQL, so I'm posting the script to do it here. If you'd like, you could make a SQL agent job and have this or something like it run periodically.

 

maintenance windows work on device ids, so first maintenance windows are resolved into CI metric ids, since that's essentially what SLOs consist of. Then SLO qos constraints (metrics) are resolved into CI metrics, since they're identified by qos, host and source combination rather than my ci metrics ids. Then the two are compared and based on that SLO exclusion periods are inserted into S_SLO_EXCLUSION_PERIODS if they're not already there.

 

This is for MSSQL. Tested with UIM 8.2.

I left some debug stuff commented out in the code as I believe that might be somewhat helpful in understanding what's happening.

 

with mwToCI as (     select         --mw.WINDOW_ID,         --ms.SCHEDULE_NAME,         mw.START_TIME,         mw.END_TIME,         --mw.DEV_ID,         --cim.ci_metric_id,         qsToCim.slo_id     from         MAINTENANCE_SCHEDULE ms,         MAINTENANCE_WINDOW mw,         CM_CONFIGURATION_ITEM ci,         CM_CONFIGURATION_ITEM_METRIC cim,         (select             qs.qos_const_id qos_const_id,             qs.slo_id slo_id,             qd.ci_metric_id ci_metric_id         from             S_QOS_CONSTRAINTS qs,             S_QOS_DATA qd         where             qd.qos = qs.qos             and             qd.source = qs.source             and             qd.target = qs.target) qsToCim     where         mw.SCHEDULE_ID = ms.SCHEDULE_ID         and         ci.dev_id = mw.DEV_ID         and         cim.ci_id = ci.ci_id         and         qsToCim.ci_metric_id = cim.ci_metric_id ), mw as (     select         --mwToCI.window_id,         --excludes.exclude_id,         --excludes.slo_id slo,         --excludes.from_date,         mwToCI.slo_id,         mwToCI.START_TIME,         mwToCI.END_TIME     from         S_SLO_EXCLUDE_PERIODS excludes         full join             mwToCI         on             excludes.slo_id = mwToCI.slo_id and excludes.from_date = mwToCI.START_TIME     where         excludes.from_date is null     ) INSERT INTO     S_SLO_EXCLUDE_PERIODS     (slo_id, from_date, to_date)     select         mw.slo_id,         mw.START_TIME,         mw.END_TIME     from         mw    

 

-jon

Attachments

    Outcomes