DX Infrastructure Management

Moving maintenance windows to SLO exclusion periods 

05-04-2015 06:28 AM

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

Statistics
0 Favorited
3 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

11-10-2015 07:49 AM

Hi Jon,

 

Thnx for the heads-up.

11-02-2015 11:40 PM

Clarification: maintenance_mode 8.00 which ships with UIM 8.2. hotfix is maintenance_mode 8.20

 

-jon

11-02-2015 08:40 AM

There's a bug in 8.20 maintenance_mode probe which causes sometimes invalid maintenance entries to be created in MAINTENANCE_WINDOW. In those entries the end date is before the start date. If such a row is moved to the SLA/SLO exclusion tables, it'll kill the sla_engine. The sla_engine will not crash or otherwise tell you that it's broken, it'll just get stuck on that calculation and will not process any further. Apparently there's a fixed version of maintenance_mode, which support has just sent me. I'll give it a test.

06-07-2015 07:47 AM

Cool nice addition!

06-05-2015 03:43 PM

Hi Jon,

 

Thnx for the sharing. We as well missed the feature for having the maintenance windows synced to the SLA engine. I picked up your script and extended it a bit, it will now add the name of the maintenance window as a note to SLA report. (This makes life easy when reading back.)

 

I think this should be the next enhancement on the maintenance probe.

Downside of scheduling the maintenance as a exluded period, you don`t have any idea how much time it represent compared to the RAW availability. Possibly a second great enhancement from CA side.

 

Cheers Rob,

 

UIM 8.2 / SQL2014

 

//CODE

 

declare  @ExcludeMW TABLE(slo_id int, START_TIME datetime, END_TIME datetime, SCHEDULE_NAME varchar(250));

 

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, 

 

   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, 

 

  mwToCI.SCHEDULE_NAME

 

   from 

 

   full join 

 

   on 

 

  excludes.slo_id = mwToCI.slo_id and excludes.from_date = mwToCI.START_TIME 

 

   where 

 

  excludes.from_date is null 

 

   )  

 

INSERT INTO 

 

   (slo_id, START_TIME, END_TIME, SCHEDULE_NAME) 

 

   select 

 

  mw.slo_id, 

 

  mw.START_TIME, 

 

  mw.END_TIME,

 

  mw.SCHEDULE_NAME

 

   from 

  

declare @field1 int

 

declare @field2 datetime

 

declare @field3 datetime

 

declare @field4 varchar(250)

 

declare cur CURSOR LOCAL for

 

   select * from @ExcludeMW

 

open cur

 

fetch next from cur into @field1, @field2, @field3, @field4

 

while @@FETCH_STATUS = 0 BEGIN

 

   INSERT INTO  S_SLO_NOTES (slo_id, note_date, note_title, note_print, note_text)

 

values ( @field1,getdate(),@field4 ,1,'' )

 

Declare @new_identity int;

 

SELECT @new_identity = SCOPE_IDENTITY()

 

INSERT INTO  S_SLO_EXCLUDE_PERIODS  (slo_id,slo_note_id,from_date,to_date )

 

values ( @field1 ,@new_identity ,@field2,@field3)

 

   fetch next from cur into @field1, @field2, @field3, @field4

 

close cur

 

deallocate cur

 

05-04-2015 11:10 AM

Note: my SLAs are simple and have 1 slo and 1qos in that slo. If you have qos from multiple devices in an SLO, you probably need to add "distinct" into the last select statement.

 

-jon

Related Entries and Links

No Related Resource entered.