Using an Incident Rule to set SLA's

jdoyle · 09-15-2006, 04:59 PM

I don't know if all the HD admins have seen this post on the 'other' Helpdesk forum, but I thought it might be useful to some so I am repeating it here. Actually this an enhanced version as now the due date will be set according to the SLA based on the start date if the workitem has been scheduled rather than the created date.

You will be nagged by the validation rules to correct the due date when saving if the start date is before the due date. You can either set a fictitious date sometime after the start date when the incident is created, or remove the following lines from the WorkItemEdit.ascx template:

Code:

If DateDiff(DateInterval.Second, dcStart.Value, dcDue.Value) < 0 Then
       IsValid = False
       AddValidationError(ResolveString("msgWorkItemInvalidStartDueDates"))
End If

Note that there is a section in the HDQUERY which must be edited to provide the information about the opening and closing times of the business, the length of this SLA, the public holidays and the days of the week that the business is closed.

The following is an assignment statement to set the due date on an incident based on an SLA. You will need to create a new Incident Rule and add the conditions for this SLA like when impact is high and urgency is high.

In the "Set these properties box" add an <Advanced Assignment>

Set: workitem_due_on

to:

Code:

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

-- THE FOLLOWING SECTION MUST BE MODIFIED TO SPECIFY THE SLA
-- Set the opening and closing times of business in minutes (in this example they are
-- set to 8am and 6pm).
set @open_at_mins = 8*60
set @close_at_mins = 18*60
-- The SLA is set in minutes (here it is set to 12 hours)
set @sla = 12*60
-- The holidays are specified in the following string. The dates must be in the format yyyymmdd.
-- In the example shows all British public holidays from 2006 to 2009.
set @holidaylist = '20060102,20060414,20060417,20060501,20060529,20060828,20061225,20061226,
20070101,20070406,20070409,20070507,20070528,20070827,20071225,20071226,
20080101,20080321,20080324,20080505,20080526,20080825,20081225,20081226,
20090101,20090410,20090413,20090504,20090525,20090831,20091225,20091228'
-- 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 CHARINDEX(CONVERT(varchar,@duedate,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@duedate)),@weekends)>0
BEGIN
SET @duedate = DATEADD(dd,1,@duedate)
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]]

Type: Text
Case sensitive: unchecked

Click OK and set "When:" to "Every time an incident is saved"

fraefel · 09-16-2006, 09:43 AM

Good job - well done !

\Thomas

knwilson · 10-04-2006, 06:25 PM

I finally got this all working except the build-in vailidation rule is still preventing a start date later than the due date when attempting to schedule the incident. I removed the following lines from the WorkItemEdit.ascx page:

If DateDiff(DateInterval.Second, dcStart.Value, dcDue.Value) < 0 Then
IsValid = False
AddValidationError(ResolveString("msgWorkItemInval idStartDueDates"))
End If
But the the only thing that happened is the color of the message displayed changed from yellow to red. LOL.

Any ideas how to get rid of this rule and allow my workers to set the start date/time to whatever? The SLA rule will then automatically set the due date/time based on the start date/time and priority.

Thanks,
Kevin

jdoyle · 10-04-2006, 06:40 PM

Thanks for catching that Kevin. I should have tested it better. This modification should sort out the problem:

Change this:

Code:

If DateDiff(DateInterval.Second, dcStart.Value, dcDue.Value) < 0 Then
   IsValid = False
   AddValidationError(ResolveString("msgWorkItemInvalidStartDueDates"))
End If

To:

Code:

If DateDiff(DateInterval.Second, dcStart.Value, dcDue.Value) < 0 Then
   dcDue.Value = dcStart.Value
End If

What this will do is set the due date to the same time as the start time if the due time is before the start time. The due time will then be processed by the incident rule and set appropriately.

knwilson · 10-04-2006, 07:06 PM

Seems to do the trick. Thanks!!!

knwilson · 10-04-2006, 08:30 PM

Thought we had it all working.. :-(

I changed the server time to be after hours to ensure the date/times would still properly get populated, but the start date/time is not using the business hours. I set the server time to 6:30pm and the ticket should have gotten a start time of 8:00am tomorrow, but it was set to 6:30pm today (current time).

Any ideas what I missed?

jdoyle · 10-04-2006, 08:36 PM

Hi Kevin,

There is no code in this Incident rule to set the start date, however I will add it if you need it.

knwilson · 10-04-2006, 08:47 PM

That would be great. We are trying to set up our rules to create the start date/time to the current time if created during business minutes and when the ticket is not scheduled. If it is created after hours, the start date/time should be 8:00am the next business day (including the holiday schedule). The due date for not scheduled is based off the created/date at this point.

On the other hadn, if the incident is scheduled, the start date/time would be manually supplied by the worker and the due date would then be calculated off it.

Please let me know if you need more info.

Thanks for tackling this for us!!!!
Kevin

jdoyle · 10-04-2006, 09:04 PM

Kevin,

I just noticed as I was preparing the start date query that I actually inadvertently omitted the code which set the SLA based on the scheduled date from the code above so I will post the corrected code for the due time along with the code for the start time.

To set the start time, add an <Advanced assignment> with Set: workitem_start_on and paste the code below into the To: box

Code:

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

-- THE FOLLOWING SECTION MUST BE MODIFIED TO SPECIFY THE SLA
-- Set the opening and closing times of business in minutes (in this example they are
-- set to 8am and 6pm).
set @open_at_mins = 8*60
set @close_at_mins = 18*60
-- The holidays are specified in the following string. The dates must be in the format yyyymmdd.
-- In the example shows all British public holidays from 2006 to 2009.
set @holidaylist = '20060102,20060414,20060417,20060501,20060529,20060828,20061225,20061226,
20070101,20070406,20070409,20070507,20070528,20070827,20071225,20071226,
20080101,20080321,20080324,20080505,20080526,20080825,20081225,20081226,
20090101,20090410,20090413,20090504,20090525,20090831,20091225,20091228'
-- 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 @duedate datetime
DECLARE @created_on datetime
SET @created_on = (SELECT CASE WHEN workitem_is_scheduled = 1 THEN workitem_start_on ELSE workitem_created_on END
FROM workitem_current_view WHERE workitem_number = WORKITEM(workitem_number))

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

SELECT @created_on]]

To set the due time, add an <Advanced assignment> with Set: workitem_due_on and paste the code below into the To: box

Code:

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

-- THE FOLLOWING SECTION MUST BE MODIFIED TO SPECIFY THE SLA
-- Set the opening and closing times of business in minutes (in this example they are
-- set to 8am and 6pm).
set @open_at_mins = 8*60
set @close_at_mins = 18*60
-- The SLA is set in minutes (here it is set to 12 hours)
set @sla = 12*60
-- The holidays are specified in the following string. The dates must be in the format yyyymmdd.
-- In the example shows all British public holidays from 2006 to 2009.
set @holidaylist = '20060102,20060414,20060417,20060501,20060529,20060828,20061225,20061226,
20070101,20070406,20070409,20070507,20070528,20070827,20071225,20071226,
20080101,20080321,20080324,20080505,20080526,20080825,20081225,20081226,
20090101,20090410,20090413,20090504,20090525,20090831,20091225,20091228'
-- 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
SET @created_on = (SELECT CASE WHEN workitem_is_scheduled = 1 THEN workitem_start_on ELSE workitem_created_on END
FROM workitem_current_view WHERE workitem_number = WORKITEM(workitem_number))

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 CHARINDEX(CONVERT(varchar,@duedate,112),@holidaylist)>0 OR CHARINDEX(CONVERT(varchar,DATEPART(dw,@duedate)),@weekends)>0
BEGIN
SET @duedate = DATEADD(dd,1,@duedate)
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]]

Let me know if this works for you.

jdoyle · 10-04-2006, 09:06 PM

Note that you will need to edit both queries for opening and closing times, holidays and weekends. The due date query requires the SLA time.

knwilson · 10-04-2006, 09:28 PM

So far so good. Seems like the rules are working perfectly and now I only have to have one rule per priority (sla).

Thank you so much. This is working perfectly.

BTW, for the emergency SLA, we are working those around the clock so my rule is much easier to set start date to created date and due date to created date offset by 4 hours.

Thanks again!!!!

Kevin

jdoyle · 10-04-2006, 09:39 PM

No problem, Kevin. In fact you helped me test and get the bugs out of the code. If you think of any other way we could improve this, let me know.

jdoyle · 12-01-2006, 10:31 PM

There was a bug which could cause short SLA's to set the due date on a Saturday for items opened on a Friday. The error has been corrected.

knwilson · 12-04-2006, 12:40 PM

Quote:

Originally Posted by jdoyle

There was a bug which could cause short SLA's to set the due date on a Saturday for items opened on a Friday. The error has been corrected.

John,

Can you point out the bug? I just discovered it this weekend as well when a 4hrs SLA created on Friday was due on Sat instead of Monday. What do I need to change in my 4hr SLA rule?

Thanks,
Kevin

jdoyle · 12-07-2006, 03:07 AM

Code:

... 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

-- Start of the missing chunk
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 of the missing chunk

WHILE @sla_days > 0
BEGIN ....

What happened was that I only ever really tested it with SLA's longer than a working day and I overlooked the case where the date which resulted from adding the fractional part of the SLA to the created date would push the due date on to a non-working day. In the case where the SLA was longer than a working day this did not matter as it was handled in the final chunk of code, but for shorter SLA's there was no check if the final date fell on a non-working day. I added the chunk of code shown above to move the calculated date/time to a working day before the code starts processing the whole day sections of the SLA.

I apologize for any inconvenience this may have caused.

knwilson · 12-07-2006, 11:58 AM

John,

Thanks for the follow-up. I have added the missing section of code.

Not sure what we would do without you!!!!!

Kevin

MikeP · 02-06-2007, 09:08 PM

Thankyou for sharing this very helpful information guys, just wondering if the original post in thread is the updated and bug-free code please? The last edit on the first post was 12/2/06 and John's last post was on the 7th.

dbush · 02-16-2007, 10:24 AM

Hey John, all,

Appreciate the code. Saved me a lot of time. I'm planning on trying to build similar logic into a Notification Policy to alert of 'x business hours left before due' to build the capability a little. Would like to know if you guys have done this already and wouldn't mind posting. If not then I'll post when I get a chance to get it.

Again, thanks.

markr1963 · 05-11-2007, 06:09 AM

Thanks, John

This has helped me loads. What changes would I need to make if I had SLAs of 24, 48, 72 hours where the customer/contact would, if creating the incident at 9am would expect a response (or receive a notification) 1, 2 or 3 days later? I'd still want to take weekends and holidays into account.

TIA

Mark

jdoyle · 05-11-2007, 07:29 AM

Hi Mark,

In that case the query used to set the due date would be a lot simpler. This should work though I haven't tested it:

Code:

HDQUERY[[
DECLARE @holidaylist varchar(1000)
DECLARE @weekends varchar(50)
DECLARE @cdt_days int

-- THE FOLLOWING SECTION MUST BE MODIFIED BY THE USER

-- CONTRACTED DELIVERY TIME<br />

-- The contracted delivery time is set in days (here it is set to 2 days)

set @cdt_days = 2

-- HOLIDAYS

-- The holidays are specified in the a string called @holidaylist.
-- The dates must be in the format yyyymmdd.
-- The example shows all British public holidays from 2006 to 2009.

set @holidaylist = '20060102,20060414,20060417,20060501,20060529,20060828,20061225,20061226, 20070101,20070406,20070409,20070507,20070528,20070827,20071225,20071226, 20080101,20080321,20080324,20080505,20080526,20080825,20081225,20081226, 20090101,20090410,20090413,20090504,20090525,20090831,20091225,20091228'

-- NON-WORKING WEEK DAYS
-- The days are specified in a string called @weekends.
-- If the service contract covers every day of the week, leave this string empty
-- Here it is set to Saturday and Sunday by retrieving the integer values for the
-- days of the week for the 6th and 7th of January, 2007.

set @weekends = CONVERT(varchar,DATEPART(dw,'20070106'))+','+CONVERT(varchar,DATEPART(dw,'20070107'))

--END OF USER-DEFINED SECTION

DECLARE @duedate datetime
SET @duedate = (SELECT CASE WHEN WORKITEM(workitem_is_scheduled) = 1 THEN REPLACE('WORKITEM(workitem_start_on)','Z','') ELSE REPLACE('WORKITEM(workitem_created_on)','Z','') END)

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

WHILE @cdt_days > 0
  BEGIN
    SET @duedate = DATEADD(dd,1,@duedate)
    SET @cdt_days = @cdt_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]]

markr1963 · 05-11-2007, 09:09 AM

Hi John

Many thanks. I've done a quick bit of testing for a 2 day SLA spanning a weekend. All good! If I wanted reports on a schedule to email the contact, assignee or whatever I'd use a notification policy in NS, yes?

Once more, thanks for your help.

Have a great weekend!

Mark

jdoyle · 05-11-2007, 09:32 AM

That would be the normal procedure.

Glad to hear it worked for you. Enjoy your weekend too.

shogo · 07-05-2007, 08:42 AM

We have office hour from 07.30-12.00 and 13.00-17.00 on monday to thursday.
On friday 07.30-12.00 and 13.00-16.00.
Is it possible to do any of this in the SLA, the most important would be the lunchbrak that occours every day.