I remember when I first got involved in SDE or Magic, as it was known back in 2002, the ability to build business rules based on events that resulted in, amongst other things, emails being sent to support staff blew me away! I had a wail of a time the following month building LOTS of business rules that sent emails all over the place. Shortly after that, Support Staff fell into two categories: those that we pleased at the level of communication / audit that the service desk software could provide and those that hated my guts! Not everyone likes having their Inbox filled it would appear.
Recently I was asked to develop a way that Support Staff could opt in to receiving emails such that, in the event of a ticket being assigned to a group, only those staff in that group who have opted in, receive an email notification. This is one of those requests that when you know how to do it it is not too bad…figuring it out on your own if you don’t know what Service Desk Express is capable of is a whole different story. So let me walk you through how you would go about this based on some previous posts and then, in subsequent posts, I show you how you can use this to create an email driven service desk.
Identifying the opted in staff
So the first requirement is to identify the opted in staff. For that, the simplest solution is to create a new field in the Support Staff module as follows:
| Setting | Value |
|---|---|
| Name | Send Email |
| Type | Numeric – Boolean |
| Default | False |
Add this field to your Support Staff form and you now have a way to identify them…
A little SQL function…
The next issue is that we need a way to use this field in a normal business rule to build a list a of those support staff who want to receive notifications. For that we need a little SQL function created as described in this earlier post. This new function will be called GET_OPTED_IN_SUPPORT_STAFF:
USE SDE GO CREATE FUNCTION "_SMDBA_"."GET_OPTED_IN_SUPPORT_STAFF" ( @GROUP 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_"."_PERSONNEL_" P JOIN "_SMDBA_"."_GROUPDET_" GD ON GD."_MEMBER_" = P."SEQUENCE" WHERE GD."_GROUP_" = @GROUP AND P."SEND_EMAIL" = 1 AND P."_INACTIVE_:" = 0 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 IF LEN(@ReturnString) > 0 BEGIN SELECT @ReturnString = SUBSTRING(@ReturnString,1,LEN(@ReturnString)-1) END RETURN @ReturnString END
UPDATED: If you look in the comments you will see a comment from Raymond Beijerling with an updated version of the SQL function that will improve performance and resilience.
Once we have created this function, it is important that we grant execute permissions to it:
GRANT EXECUTE ON [_SMDBA_].[GET_OPTED_IN_SUPPORT_STAFF] TO PUBLIC
Finally we just need a simple business rule that, on change of group assignment for example, sends an email to all opted in support staff:
HD – Notify Opted In Group Members When Incident Assigned To Group
Conditions
when a Incident Create Update occurs with
| Expression 1 | Comparison Operator | Expression 2 | Logical Operator |
|---|---|---|---|
| {DB,Incident,Group Name,”Incident #” = {TR,Incident #}} | NotEqual | {CUR,Group Name} | And |
| {DB,Incident,Group Name,”Incident #” = {TR,Incident #}} | NotEqual | And | |
| {MATH,(SELECT LEN(_SMDBA_.GET_OPTED_IN_SUPPORT_STAFF({TR,Seq.Group})))} | GreaterThan | 0 |
Actions Action 1
| Method | Module | Form |
|---|---|---|
| Create | Notifications | Multi-Notifications |
| Field Name | Value |
|---|---|
| Subject | Service Desk Notification – Incident Assigned To Your Group |
| Body | <HTML><HEAD><TITLE>Service Desk Notification – Incident Assigned To Your Group</TITLE></HEAD><BODY><P>Add some nice text here</P></BODY></HTML> |
| Notification | SMTP Email |
| Address Staff Multiple | {MATH,(SELECT _SMDBA_.GET_OPTED_IN_SUPPORT_STAFF({TR,Seq.Group}))} |
Summary
That’s pretty much it…Now no matter what group a support staff member is a member of, if they have their Send Email flag set then they will receive emails when an incident is assigned to their group. As always I hoped this post proved useful. Feedback, positive or negative, is always hugely appreciated.
JOATIT 