Tag Archives: user-defined functions

Standard

Posted by

Posted on

September 30, 2011

Posted under

Service Desk Express

Comments

6 Comments

Allowing Staff To Opt In To Emails

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.

Standard

Posted by

Posted on

October 19, 2009

Posted under

Service Desk Express

Comments

5 Comments

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.

Standard

Posted by

Posted on

August 3, 2007

Posted under

Service Desk Express

Comments

4 Comments

Purchase Request Line Items in Email

One of the difficulties with the Purchasing Module that I have found is the inability to easily send the request to the Vendor (or for that matter the customer/approver) including the line items. Well everything is possible of course so I thought I would post a solution for comment/criticism:

You need to create a simple SQL function something like the one attached: Function GET_PR_LINE_ITEMS

Then grant permissions to Public to Execute this function.

Finally use the function in your business rule e.g. When PR is updated with a change of status from Approved to Ordered send email.

In the body of the email you would then have:

Line Items:
{MATH, (SELECT "_SMDBA_"."GET_PR_LINE_ITEMS"({TR,Req #}))}

What basically happens is that the function concatenates all the line items into a single string that can then be used in the body of an email.

I have also uploaded created a function that returns an HTML table row for every line item: Function GET_PR_LINE_ITEMS_HTML. To use this function you would, in the body of the notification you would have…

<table border="1"><tr><td colspan="4">Line Items</td></tr>{MATH, (SELECT "_SMDBA_"."GET_PR_LINE_ITEMS_HTML"({TR,Req #}))}</table>

Hope this maybe of use.

Standard

Posted by

Posted on

June 27, 2007

Posted under

Service Desk Express

Comments

Leave a comment

Using UDF’s in Business Rules

In my last post I created a User-Defined Function (UDF) to determine if a given datetime fell within or outside working hours (as defined by a given work schedule). This post will discuss how to use a function such as this in business rules.

Ok so lets say you want to check in a business rule (doesn’t matter whether normal or client side) if the current time is within the work schedule. For the sake of argument we will assume you have a work schedule called NORMAL.

All you need to do is add the following Condition:

{MATH, (SELECT "_SMDBA_"."IsWorkTime"('NORMAL', GETDATE()))} EQUAL TO 1

Consequently the business rule you are creating will only fire if the current time falls within the selected work schedule.

Hope this helps.

Standard

Posted by

Posted on

June 27, 2007

Posted under

Service Desk Express

Comments

1 Comment

Function to check if datetime is in work time

A couple of posts recently in the Yahoo MagicSolutions forum made me realise that there was no easy way of checking if a given datetime was during or outside working hours (as defined by the work schedule). Consequently I set about writing a UDF that would accept a work schedule name and datetime as an input and return 1 if the time fell within the given work schedule and 0 if it did not.

Because of the way things get formatted using WordPress I have saved the function as a text document available here: IsWorkTime Function

Depending on where you want to use UDF’s in Service Desk Express you need certain permissions to do so. Personally, whenever I create a function like this (assuming it is not returning any privaleged information) I grant execute permissions to everyone. To do this simply run the following SQL:

GRANT EXECUTE ON [_SMDBA_].[IsWorkTime] TO Public

In my next post I’ll discuss how to use UDF’s in business rules…