How To Send An Email To All Assessors For A Given Change

Ever wondered how to communicate with all assessors and/or approvers of a change request that the status of a change has changed? If the answer is “Yes” you will, probably before now, have realised that this is not as straightforward as it seems.

What I hope to achieve in this post is to walk you through how you would achieve this. The problem lies in Service Desk Express’s lack of functionality to “iterate” through a dataset and complete an action for each record it finds.

Solution

The solution is courtesy of a little SQL magic – we are going to create a user-defined function that iterates through each of the assessors and builds a string of their Login IDs such that we can give this to a Service Desk Express business rule to send an email:

CREATE FUNCTION [_SMDBA_].[GET_CHANGE_ASSESSORS]
(
@ChangeNo int
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @ReturnString nvarchar(4000)
SET @ReturnString = ''
DECLARE @LoginID nvarchar(30)
DECLARE cur CURSOR
FOR SELECT DISTINCT P.CODE FROM    _SMDBA_._CHANGEASMT_ CA JOIN _SMDBA_._PERSONNEL_ P ON P.SEQUENCE = CA.ASSESSOR WHERE CA.[_INACTIVE_:] = 0 AND P.[_INACTIVE_:] = 0 AND CA.[CHANGE] = @ChangeNo
OPEN cur
FETCH NEXT FROM cur INTO
@LoginID
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @ReturnString = @ReturnString + @LoginID + ';'
END
FETCH NEXT FROM cur INTO
@LoginID
END
CLOSE cur
DEALLOCATE cur
RETURN SUBSTRING(@ReturnString,1,LEN(@ReturnString)-1)
END

It is important that we grant execute permissions to this function:

GRANT EXECUTE ON [_SMDBA_].[GET_CHANGE_ASSESSORS] TO PUBLIC

Then we just need a simple business rule that, on change of status for example, sends and email to all assessors:

Change Request -Notification of Change of Status To Assessors

Conditions

when a Change Request

Update occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
{CUR,Status ID} NotEqual {TR,Status ID} None

Actions

Action 1

Method Module Form
Create Notifications Multi-Notifications
Field Name Value
Subject Service Desk Notification – Change Request Status Update
Body <HTML>
<HEAD>
<TITLE>Service Desk Notification – Change Request Status Update</TITLE>
</HEAD>
<BODY>
<P>Add some nice text here</P>
</BODY>
</HTML>
Notification Module Descr SMTP Email
Address Staff Multiple {MATH,(SELECT _SMDBA_.GET_CHANGE_ASSESSORS({TR,Sequence}))}

Summary

User-defined functions can be a goldmine of pieces of functionality that is missing from Service Desk Express. So before you despair…think UDF! As always I hoped this post proved useful. Feedback, positive or negative, is always hugely appreciated.

Advertisements

5 thoughts on “How To Send An Email To All Assessors For A Given Change

  1. Great site. The IT group I’m in started using SDE in March and we’re working our way through customizing it as we go along.

    One thing I wish we could do is lauch differing forms from a quickview on the same module…based on the Work Type field.

    There is a preventative work order and a normal work order. They have different forms, but a quickview that shows them both lauches the default work order form…

  2. Hi R3,

    Many thanks for the compliments. Much appreciated. I agree that it would be good to have a similar type functionality to Incident Types with Work Orders and other modules. I think there is a vendor who has done some custom work with that but can’t remember who.

    The closest you can get out of the box is to have a single Work Order form and then use CSBR’s to change the look of the form based on the value of the Work Type.

    Best regards,

    Alan

  3. Been trying to create a UDF based on your example here but could not do it. I need to notify members of a group that a particular incident has been picked up by one of their members (Assign to Myself). Could you give me ideas on how to go about this? Thank you.

  4. Hi Eric,

    I don’t think you need a UDF to do this as all the people you want to notify are in the same Group which is supported out of the box. Create a business rule that on Update of an Incident with {TR,Assigned To Login ID} NotEqual {CUR,Assigned To Login ID} AND {TR,GroupName} Equals {MAGICUSERSGROUP}; Send Notification to {MAGICUSERSGROUP} which will be the group name of the logged on user’s group.

    I think that should cover it. Let me know how you get along.

    Regards,

    Alan

  5. Thank you Alan.

    Doing what you have suggested will also notify the staff who assigns the incident to himself. I was looking into implement a UDF that will exclude the assigned to staff from being sent a notification.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s