jonhcw

Some helpful SQL scripts for SLA management

Blog Post created by jonhcw on Sep 2, 2015

Here are some helpful SQL scripts that I use when working with SLAs.

 

Set SLA exclusion period

Sometimes I need to add an exclusion period to every SLA of an account, for example when there's a scheduled maintenance break. Here's a useful little piece to do just that:

insert into S_SLA_EXCLUDE_PERIODS (sla_id, from_date, to_date)
  select sla_id, '2015-09-01 00:00:00.000', '2015-09-01 17:00:00.000' from CM_ACCOUNT_SLA where account_id = 7






I believe it's pretty self-explanatory; pretty much the only thing you need to do is adjust your period and fetch the correct account_id from CM_ACCOUNTS

 

Show SLAs for an account

This little script shows you all the SLAs for a given account for a given period. This will only work for historic data, not running month. You will need to configure "account_name" and "end_date" at the beginning

declare
  @account_name varchar(50),
  @account_id int,
  @end_date varchar(10)

set @account_name = '<ACCOUNT>' --Set account name here
set @end_date = '6/1/2015' -- Set SLA period here
select @account_id = account_id from CM_ACCOUNT where name = @account_name


select
  sladef.name as 'SLA name',
  grpdef.name as 'SLA folder', -- I get this, because I divide SLAs in to folders where folders are contracts
  slacmp.percentage as 'SLA achieved',
  slacmp.compliance_percentage as 'SLA threshold',
  slacmp.period_begin as 'Period start',
  slacmp.period_end as 'Period end'
from
  H_SLA_COMPLIANCE as slacmp,
  S_SLA_DEFINITION as sladef,
  S_GROUP_DEFINITION as grpdef,
  S_GROUP_SLA as grpsla,
  CM_ACCOUNT_SLA as accsla


where
  slacmp.sla_id = accsla.sla_id
  and
  sladef.sla_id = accsla.sla_id
  and
  grpsla.sla_id = accsla.sla_id
  and
  grpdef.group_id = grpsla.group_id
  and
  accsla.account_id = @account_id
  and
  slacmp.period_end = @end_date






 

QoS outage occurrence time and duration

Strictly speaking not an SLA thing, but this can sometimes be pretty useful. Essentially it is the same information given to you in UMP's SLAReports portlet's outage times. Unfortunately that info is not persisted anywhere in the database, so sometimes it's useful to query for it. This is not a very advanved script, so it'll require some tuning: You need to specify the correct RN table for your qos, the table_id for your metric, the period in sampletime and, at the end, the threshold the sampletimes need to exceed for them to be counted as outages. Generally that's your samplerate + some tolerance.

;with cur as (
select
  ROW_NUMBER() OVER(ORDER BY SAMPLETIME ASC) rn,
  *
from
  RN_QOS_DATA_0003 as [current]
where
  table_id = 24031
  and
  sampletime >= '2015-07-01 00:00:00'
  and
  sampletime < '2015-08-01 00:00:00'
)
select
  a.rn 'a',
  b.rn 'b',
  a.table_id,
  a.sampletime,
  b.sampletime,
  datediff(second, a.sampletime, b.sampletime) [diff]
from
  cur a
inner join
  cur b
ON
  a.rn = b.rn - 1
where
  datediff(second, a.sampletime, b.sampletime) > 301






 

Recalculate multiple SLAs

This here is a very useful script that can save you a lot of trouble. It recalculates SLAs based on a query, meaning you can adjust it to recalculate, for example, an accounts SLAs, all SLAs, etc. You need to adjust the account_id, month, and year variables at the beginning. For this one, I actually got the part within the cursor from support.

declare @id int,
       @month int,
       @year int,
       @accountid int,
       @period_begin datetime,
       @period_end datetime

  -- account
  select @accountid = 2

  -- number of month to calculate for
  select @month = 5

  -- year
  select @year = 2015

  select @period_begin = DateAdd(day, 0, DateAdd(month, @month - 1, DateAdd(Year, @year-1900, 0)))
  select @period_end = DateAdd(month, 1, @period_begin)
  declare c cursor for
    select sla_id from cm_account_sla where account_id = @accountid
  open c
  fetch next from c
    into @id

  WHILE @@FETCH_STATUS = 0
    BEGIN

      insert into d_sla_jobs (job_state, owner, description, create_date, execute_date, expire_date, report, history, sla_id, period_begin, period_end, description_token) VALUES
        (0,
        'SYSTEM',
        'Automatic',
        GETDATE(),
        DATEADD(minute,1,GETDATE()),
        DATEADD(day,1,GETDATE()),
        0,
        1,
        @id,
        @period_begin,
        @period_end,
        'rs#sla_engine.sla_description.automatic')
      fetch next from c into @id
    end
  close c

  deallocate c






Note that this will calculate "historic" data. If you want to recalculate the running month, you need to set the 1 => 0 before @id towards the end of the script.

 

Maintenance windows to SLO exclusion

I've also previously posted a document on moving Maintenance windows to SLO exclusions

 

Hopefully these will be helpful!

Outcomes