Endpoint Management & Virtualization ArticlesRSS

Auto Scheduling Incidents by Priority Assignment

DeborahAlbrecht

June 2nd, 2008

Filed under: Helpdesk Solution, Configuring, Endpoint Management and Virtualization

The good thing about the below incident rules is that they are easy to change for your companies needs. We base our scheduling on priority, however, if you base it off urgency or impact it is not difficult to replace the priority with either of those.

One item to keep in mind is that holidays specified in the rules are US market holidays through 2009. Also, we are a M-F 7-5 Helpdesk Support, so Saturdays and Sundays are excluded within our incident rule.

I am only including 2 of the priority schedules as the others just need to be tweaked to your Service Level Agreements set between you and your customers/end-users. The two included are Low and ASAP. Our ASAP is included because it is "special" in the sense that there are 2 ASAP assignments for us, (1) during market hours and (2) after market hours. We have this since we are a financial firm anything during market that is defined as ASAP is hindering our business.

Low Priority Scheduling Incident Rule

Create a new Incident Rule
Name your rule.

Set these 3 properties separately using the Add button:

set Start Date to "workitem_created_on"
set Schedule to "is_scheduled" = true
set "workItem_due_on" to (insert SQL below)

HDQUERY[[declare @open_at_mins int 
declare @close_at_mins int 
declare @sla int 
declare @holidaylist varchar(1000) 
declare @weekends varchar(50) 

-- The following must be modified to match your SLA
-- Set the opening and closing times of business in minutes. (Our business hours for Support is 6 AM to 6 PM ET)
set @open_at_mins = 6*60 
set @close_at_mins = 18*60 
-- The SLA is set in minutes
set @sla = 480*60 
-- The dates must be in the format yyyymmdd. These are all market holidays from 2008 to 2009. 
set @holidaylist = '20080101, 20080121, 20080218, 20080321, 20080526, 20080704, 20080901, 20081127, 20081225, 20090101, 20090119, 20090216, 20090410, 20090525, 20090703, 20090907, 20091126, 20091225'
-- Days of the week that do not count towards this SLA. The days are specified in a string. 
-- In the example, Saturday (7) and Sunday(1) are chosen. If the SLA covers every day of the week 
-- leave this string empty 
set @weekends = '1,7' 
--END OF USER-DEFINED SECTION 


declare @working_mins int 
set @working_mins = @close_at_mins - @open_at_mins 

declare @sla_mins int 
declare @sla_days int 
set @sla_days = @sla/@working_mins 
set @sla_mins = @sla%@working_mins 

declare @duedate datetime 
DECLARE @created_on datetime 
SELECT @created_on = REPLACE('WORKITEM(workitem_created_on)','Z','') 

SET @created_on = CASE 
WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) < @open_at_mins 
THEN DATEADD(mi,@open_at_mins,CONVERT(datetime,CONVERT(varchar,@created_on,112))) 
ELSE @created_on 
END 

SET @created_on = CASE 
WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) > @close_at_mins 
OR CHARINDEX(CONVERT(varchar,@created_on,112),@holidaylist)>0 
OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@created_on)),@weekends)>0 
THEN DATEADD(mi,@open_at_mins,CONVERT(datetime,CONVERT(varchar,DATEADD(dd,1,@created_on),112))) 
ELSE @created_on 
END 

WHILE CHARINDEX(CONVERT(varchar,@created_on,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@created_on)),@weekends)>0 
BEGIN 
SET @created_on = DATEADD(dd,1,@created_on) 
END 

SET @duedate = CASE 
WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) + @sla_mins > @close_at_mins 
THEN DATEADD(mi,(24*60)-@working_mins+@sla_mins,@created_on) 
ELSE DATEADD(mi,@sla_mins,@created_on) 
END 

WHILE @sla_days > 0 
BEGIN 
SET @duedate = DATEADD(dd,1,@duedate) 
SET @sla_days = @sla_days-1 
WHILE CHARINDEX(CONVERT(varchar,@duedate,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@duedate)),@weekends)>0 
BEGIN 
SET @duedate = DATEADD(dd,1,@duedate) 
END 
END 
SELECT @duedate]]

set When to "Every time incident is saved"
set And function to "When ALL of these are TRUE"
1. "Priority" is equal to "Low"
  - add this using the Add button

Dual ASAP Priority Scheduling Incident Rule

This will schedule an incident's due date/time for 1 hour after creation during 6AM-6PM hours (all in ET) and 2 hours after 6 PM to 6 AM the following day.

Create a new Incident Rule
Name your rule.

Set these 3 properties separately using the Add button:

set Start Date to "workitem_created_on"
set Schedule to "is_scheduled" = true
set "workItem_due_on" to (insert SQL below)

HDQUERY[[declare @open_at_mins int 
declare @close_at_mins int 
declare @sla int 
declare @holidaylist varchar(1000) 
declare @weekends varchar(50) 
declare @market int

-- The following must be modified to match your SLA
-- Set the opening and closing times of business in minutes (in this example they are 
-- set to 8am and 6pm EST). 
set @market = datepart(hh,getdate())

if @market >= 8 and @market <= 18 
 begin
 --market hrs
 set @open_at_mins = 8*60 
 set @close_at_mins = 18*60 
 -- The SLA is set in minutes (here it is set to 1 hour) 
 set @sla = 1*60 
 end
else
 begin 
 -- Set the opening and closing times of business in minutes (in this example they are set to 6pm and 7am EST)
  set @open_at_mins = 18*60 
  set @close_at_mins = 7*60 
  -- The SLA is set in minutes (here it is set to 2 hours) 
  set @sla = 2*60
 end
 
-- HOLIDAY The dates must be in the format yyyymmdd. 
-- all Financial holidays from 2008 to 2009. 
set @holidaylist = '20080101, 20080121, 20080218, 20080321, 20080526, 20080704, 20080901, 20081127, 20081225, 20090101, 20090119, 20090216, 20090410, 20090525, 20090703, 20090907, 20091126, 20091225'

-- Days of the week that do not count towards this SLA. The days are specified in a string. 
-- In the example, Saturday (7) and Sunday(1) are chosen. If the SLA covers every day of the week 
-- leave this string empty 
set @weekends = '1,7' 
--END OF USER-DEFINED SECTION 

declare @working_mins int 
set @working_mins = @close_at_mins - @open_at_mins 

declare @sla_mins int 
declare @sla_days int 
set @sla_days = @sla/@working_mins 
set @sla_mins = @sla%@working_mins 

declare @duedate datetime 
DECLARE @created_on datetime 
SELECT @created_on = REPLACE('WORKITEM(workitem_created_on)','Z','') 

SET @created_on = CASE 
WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) < @open_at_mins 
THEN DATEADD(mi,@open_at_mins,CONVERT(datetime,CONVERT(varchar,@created_on,112))) 
ELSE @created_on 
END 

SET @created_on = CASE 
WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) > @close_at_mins 
OR CHARINDEX(CONVERT(varchar,@created_on,112),@holidaylist)>0 
OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@created_on)),@weekends)>0 
THEN DATEADD(mi,@open_at_mins,CONVERT(datetime,CONVERT(varchar,DATEADD(dd,1,@created_on),112))) 
ELSE @created_on 
END 

WHILE CHARINDEX(CONVERT(varchar,@created_on,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@created_on)),@weekends)>0 
BEGIN 
SET @created_on = DATEADD(dd,1,@created_on) 
END 

SET @duedate = CASE 
WHEN DATEPART(hh,@created_on)*60+DATEPART(mi,@created_on) + @sla_mins > @close_at_mins 
THEN DATEADD(mi,(24*60)-@working_mins+@sla_mins,@created_on) 
ELSE DATEADD(mi,@sla_mins,@created_on) 
END 

WHILE @sla_days > 0 
BEGIN 
SET @duedate = DATEADD(dd,1,@duedate) 
SET @sla_days = @sla_days-1 
WHILE CHARINDEX(CONVERT(varchar,@duedate,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@duedate)),@weekends)>0 
BEGIN 
SET @duedate = DATEADD(dd,1,@duedate) 
END 
END 
SELECT @duedate]] 
--the above is all run off the stipulation that the asap priority assignment has been made
when priority = 'ASAP'

set When to "Every time incident is saved"
set And function to "When ALL of these are TRUE"
1. "Priority" is equal to "ASAP"
  - add this using the Add button

GOOD LUCK!

Note: I have attached the SQL for each of these at TXT files for your convenience.

0 votes

Login or register to post comments
Comments RSS Feed

Auto Scheduling Incidents by Priority Assignment

Low Priority Scheduling Incident Rule

Dual ASAP Priority Scheduling Incident Rule

Share This

Community Feed