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!