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