January 27, 2007

Contracted Delivery Times

Using incident rules to set the delivery times on Helpdesk incidents.

The original code in this article was developed from a request on the Helpdesk forum for an incident rule that would set the contracted delivery date on an incident while accounting for business hours, holidays and weekends. The task looked daunting at first until I started to think about it. Later additions were added after questions on the Altirigos forums. Setting the start date is optional and you may skip this if you feel it is not required.

The contract between the service desk and the users often specifies service delivery times based on parameters such as Priority, Impact and Urgency. This article will show you how to write Incident Rules to set the start and due dates of incidents according to the contracted service delivery times. There are many other ways this could be implemented in the code, but this approach requires a minimum of customization.

As an example, the Incident Rule shown here will be based on a service contract that specifies that all Medium Priority incidents should be resolved in 6 working hours. The business is open from Monday to Friday, starting at 8am and closing at 6pm everyday. In this case, an incident opened at 4pm on a Friday would be due at 1pm on the following Monday or even Tuesday if it is a long weekend.

Incident Rules

An Incident Rule is a process that runs when incidents are created or edited. If the incident properties match the conditions specified, the incident is modified as defined by the rule. In this case, I want the start and due dates for the incident to be set based on the values defined in the service contract. If the incident is scheduled to start sometime in the future then we must respect this time, otherwise the start time is set to the time the incident was created or to the opening time of business on the next working day if the incident is created outside of working hours. The due date is calculated from the start date and must account for the business opening and closing hours including weekends and holidays.

The HDQUERY macro allows you to run SQL queries against the Helpdesk database. A query used in a HDQUERY macro should return a single value.

In order to acheive our goal we will use two SQL queries, one for the start time and one for the due time. We need to set a few variables in order to be able to calculate the correct times.

  1. Opening and closing times of business

    2. The opening and closing times are set in minutes from the start of the day

  2. Weekends

    3. Days of the week when the business is closed is set as a list of integers. In SQL server, by default (US) the days are numbered from 1 to 7 starting on Sunday. These values however may be changed on the server so it is safer to calculate the day numbers from known dates. If the business does not close during the week then this string may be left empty.

  3. Holidays

    4. A list of days during the year when the business is closed. The dates must be specified in ISO format, i.e. 25th of December 2006 must be entered as 20061225.

The SQL Queries

  1. Start date

    2. The start date query takes the created date of the incident and checks if it is in working hours on a working day. If not, it calculates the opening time of business on the next working day. If the incident was scheduled, no change is made to the start date. Take the code below, paste it into a text editor like Notepad and make the changes necessary to match your service contract and business opening hours.

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

    -- THE FOLLOWING SECTION MUST BE MODIFIED BY THE USER

    -- OPENING AND CLOSING TIMES
    -- 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

    -- 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 @working_mins int
    set @working_mins = @close_at_mins - @open_at_mins

    DECLARE @created_on datetime

    IF WORKITEM(workitem_is_scheduled) = 1
       SET @created_on = REPLACE('WORKITEM(workitem_start_on)','Z','')
    ELSE
    BEGIN
    SET @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
    END
    SELECT @created_on]]

  2. Due Date

    3. The due date query is similar to the start date query. The query calculates the starting time as the creation time or the scheduled time. The start time is set to opening time on the next business day if either of these are outside of business hours. It then adds on the delivery time and ensures that the date is a working day.

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

    -- THE FOLLOWING SECTION MUST BE MODIFIED BY THE USER

    -- OPENING AND CLOSING TIMES

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

    -- CONTRACTED DELIVERY TIME<br />

    -- The contracted delivery time is set in minutes (here it is set to 6 hours)
    set @cdt = 6*60

    -- 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 @working_mins int
    set @working_mins = @close_at_mins - @open_at_mins

    DECLARE @cdt_mins int
    DECLARE @cdt_days int
    set @cdt_days = @cdt/@working_mins
    set @cdt_mins = @cdt%@working_mins

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

    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) + @cdt_mins > @close_at_mins
    THEN DATEADD(mi,(24*60)-@working_mins+@cdt_mins,@created_on)
    ELSE DATEADD(mi,@cdt_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 @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]]

The WorkitemEdit Template

As the due date is calculated from the start date and we want to allow workers the option of scheduling the start date, we will need to edit the WorkItemEdit.ascx template. This is not strictly necessary, but if you don't do it then workers will have to set a due date for the incident which is greater than or equal to the start date or they will get a validation error when they try and save the incident. As the due date is being calculated, requiring the workers to set this date is a waste of their time so we will edit the template and change the validation code a little.

If you have already created a custom copy of the WorkItemEdit.ascx template then you may skip the following instructions and edit your custom copy as explained below.

To create a custom copy of the WorkItemEdit.ascx file:

  1. Create a new folder in the C:\Program Files\Altiris\Helpdesk\AeXHD directory called custom.
  2. Copy the file called WorkItemEdit.ascx from the templates folder to the new custom folder and rename it customWorkItemEdit.ascx. It is not necessary to rename the file, but it will help you remember that this is your copy of the original.
  3. Using a text editor like Notepad create a file in the AeXHD folder called custom.config and add this code to the file:

    4. <?xml version="1.0" encoding="utf-8" ?>
       <custom.configuration>
          <files path="~/custom/">
             <file id="WorkItemEdit" file="customWorkitemEdit.ascx" />
          </files>
       </custom.configuration>

  4. Restart the IIS Service on the server. (An easy way to do this is to go to Start > Run. Enter 'iisreset' and press OK).

Edit your custom copy of the WorkItemEdit file and find the lines:

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

Change these so that they read:

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

Create the Incident Rule

To write the new Incident Rule, access the Helpdesk Console as an administrator. From the navigation menu, select Admin > Incident Rules > New Incident Rule. In the first section. enter a name for the rule, and a short description in the comment field (see Figure 1).

In the second section, select <Advanced assignment> from the dropdown list and click 'Add'. A new screen will open. Enter workitem_start_on in the text field labeled "Set:". Cut and paste the SQL query for the start date shown above into the text field labeled "To:". Leave the "Type" set to "Text" and click OK to return to the previous screen.

Add another <Advanced assignment> with workitem_due on in the "Set" text field and paste the SQL query for the due date in the "To" text field. Click OK to finish.

The final stage is specifying the conditions under which this will run. Set "When" to "Every time incident is saved" . Since we want this to to run when the Priority is medium, select Priority from the dropdown and click Add. Enable When the value of "Priority": and select "is equal to" and "Medium". Click OK to save. Mark the checkbox next to Active to enable the rule, and disable the checkbox next to Terminal. Finally click on OK to save the rule.

 

Now everytime an incident saved with the priority set to Medium the rule will be run and the start and due dates will be set.