Standard

Posted by

Posted on

October 30, 2011

Posted under

Service Desk Express

Comments

2 Comments

Making the SDE Knowledge Base work for you

For me, out of the box, the Knowledge Base / Knowledge Search function in Service Desk Express (SDE) doesn’t work! In fairness, the reason for this is not entirely an issue with the software but more about how organisations, or more specifically support staff, use SDE. Let me explain:

Most organisations I have worked with have a simple business rule that sends a notification to the client when an incident is resolved. Frequently, that notification includes the Incident Resolution and as such, the words that go into that Incident Resolution are more like “User able to print again” as opposed to “Stopped the Print Spooler service, deleted the stuck print job, and restarted the Print Spooler service on HOMPR01.” Unfortunately, the text you want to see in your Knowledge Base as a newbie support staff member trying to fix their first stuck print queue is the latter NOT the former.

So what’s the solution…For me the easiest solution is to create a new field in the Incident module as follows:

Setting Value
Name Work Instructions
Type Text – Variable Length

With this field added to the form, you can now advise your support staff to populate the appropriate fields e.g.

  • Incident Description: User unable to print to printer Q1000111.
  • Work Instructions: Stopped the Print Spooler service, deleted the stuck print job, and restarted the Print Spooler service on HOMPR01.
  • Incident Resolution: User able to print again.

Now to get the real benefit from this you need to reconfigure the Knowledge Search settings available from System Administration > Knowledge Search Settings.

Search Fields:

Module Name Field Name
Incident Incident Description
Incident Incident Resolution
Problem Management Problem Description
Problem Management Problem Resolution
Incident Work Instructions

Display Fields:

Module Name Field Name Solution
Incident Incident Description  
Incident Incident Resolution Yes
Problem Management Problem Description  
Problem Management Problem Resolution Yes
Incident Work Instructions  

 

“Not entirely an issue with the software…”

There is however one issue with the software that is truly pants! By default, when your support staff (or employee for that matter) uses the Knowledge Base they will only get 50 results returned. This would be reasonably logical if it wasn’t for the fact that it is 50 total results as opposed to what is should be: 50 results from Incidents; 50 results from Problems; 50 results from Changes; 50 results from FAQs. So you have a choice: Educate your users OR Hack! I prefer the latter as I want the functionality used. As such, if you are going to do this, MAKE SURE YOU BACK UP EVERYTHING (DB and Application Server directories) FIRST!!!

Open SirSearchPage.htm (C:Program FilesBMCService Desk ExpressApplication Serverhtml by default) in Notepad and find the line 39:

<INPUT TITLE=MGC_PL_LABEL_4057 Value="50" CLASS…>

Replace with:

<INPUT TITLE=MGC_PL_LABEL_4057 Value="5000" CLASS…>

Save the file.

IISRESET for good measure and you should be good to go. As always any feedback (positive or negative) is always welcome.

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

August 21, 2011

Posted under

Service Desk Express

Comments

3 Comments

Service Tree By Description Rather Than Name

As you will no doubt be aware, Service Desk Express (SDE) provides the ability to display the Category list (aka Support Subjects) and / or the Service list, in a treeview as opposed to the normal popup list. In case you weren’t aware of this functionality it is achieved by checking the Category Tree / Service Tree checkboxes for these appropriate fields in Form Customisation.

By design / default however, whilst the Category tree displays the Category Description, the Service tree displays the Service Name. Recently, I was asked if it was possible to display the Service tree by Service Description instead of Service Name. As with most things, it IS possible but does require a couple of code hacks as detailed below. As such, if you are going to do this, MAKE SURE YOU BACK UP EVERYTHING (DB and Application Server directories) FIRST!!!

To implement this change we need to alter two files. This first of these is ServiceTreeRender.aspx (located in C:Program FilesBMCService Desk ExpressApplication Server by default). Open the file in Notepad and change the lines:

string sColName = (Fields.GetFieldBySequence(6) as NAMMETADATALib.IMField).name; //Service Name
...
kidList[z] = new SDE.UI.TreeNode(objOneData.get_Item(1).ToString(), //id
objOneData.get_Item(6).ToString(), //name
Convert.ToInt32(objOneData.get_Item(14)) > 0, //Check for children
null,
((Convert.ToInt32(objOneData.get_Item(14)) > 0) ? "folder":""),
false); //Sudha, fix for bug#34382, adding customNodeData value

to be

string sColName = (Fields.GetFieldBySequence(7) as NAMMETADATALib.IMField).name; //Service Description
...
kidList[z] = new SDE.UI.TreeNode(objOneData.get_Item(1).ToString(), //id
objOneData.get_Item(7).ToString(), //description
Convert.ToInt32(objOneData.get_Item(14)) > 0, //Check for children
null,
((Convert.ToInt32(objOneData.get_Item(14)) > 0) ? "folder":""),
false); //Sudha, fix for bug#34382, adding customNodeData value

Save the file.

The other file is xml_generator.aspx located in the same directory. Open this in Notepad and find the GetServiceTree() function. Change the lines:

string sColName = (objFields.GetFieldBySequence(6) as NAMMETADATALib.IMField).name;
...
objFetchData.set_Item(6, "");
...
strxml.Append(SDE.Utility.encodeURIComponent(objOneData.get_Item(6).ToString() )); //name

to be

string sColName = (objFields.GetFieldBySequence(7) as NAMMETADATALib.IMField).name;
...
objFetchData.set_Item(7, "");
...
strxml.Append(SDE.Utility.encodeURIComponent(objOneData.get_Item(7).ToString() )); //description

Save the file.

IISRESET for good measure and you should be good to go. As always any feedback (positive or negative) is always welcome.

Standard

Posted by

Posted on

August 19, 2011

Posted under

General

Comments

7 Comments

Apologies – I’m back…

This is just a quick post to offer my apologies for the lack of updates on this site over the past YEAR! Whilst it is true to say that I have been busy with work, the honest truth is that I haven’t prioritised the site the way I should have and, as a result, have lost readers etc. Time to move on…

Anyway, I am back and writing…New Service Desk Express article going up this weekend.

Please, please, please…If you have any ideas of what you would like to see posts / projects about, please let me know.

Standard

Posted by

Posted on

May 1, 2010

Posted under

SQL Server

Comments

Leave a comment

Maintenance Plan for SQL Server 2005 Express

There’s no such thing as a free lunch

I recently visited a site where the local IT Team were having to remember to manually backup a SQL Server 2005 Express Edition database each night, as one of the missing features of the Express Edition is the ability to set up a maintenance plan to do this automatically. What follows is a workaround I provided to automate their database backups:

Solution

First off I created a really simple batch file using Notepad:

@echo off
Set YYYY_MM_DD = %DATE:~6,4%_%DATE:~3,2%_%DATE:~0,2%
Set DBName = SDE
set BackupFile = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackup%DBName%_%YYYY_MM_DD%.bak
osql -S ServerName -E -Q "BACKUP DATABASE %DBName% TO DISK='%BackupFile%'"

I saved the file as C:Program FilesBatch JobsDBBackup.bat

All the batch file is doing is running an SQL command to backup the database to disk appending the current date automatically. All that is then left is to create a Scheduled Task to run the batch file every night.

Clearly there is much more you could do with this and indeed there are a whole raft of applications (many free) that allow you to do this with a GUI etc. but…just in case it helps.

As always any criticism, positive or negative is always welcome.

Standard

Posted by

Posted on

January 31, 2010

Posted under

Service Desk Express

Comments

Leave a comment

New Starter/Leaver Process – Part 3

In my previous posts in this series (New Starter/Leaver Process – Part 1 and New Starter/Leaver Process – Part 2) I described, diagrammatically, a new starter business process that could be implemented in Service Desk Express as shown below:

New Starter

In the final part of this series I am going to finish it off with the business rules that tie everything together. Before I do so I should mention that I have made some changes to the Function GET_STD_CONFIGS detailed in the previous post. So if you downloaded this before please go ahead and download it again and run it against your Service Desk Express database.

The first business rule we need is to create the new starter ticket (incident) when a client is created:

CL01 – Client Insert Create New Starter Ticket

Conditions

when a Client

Create occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
No Conditions

Actions

Action 1

Method Module Form
Create Incident Incident (base)
Field Name Value
Client ID {TR,Client ID}
Company ID {TR,Company ID}
Group Name HELPDESK
Subject ID ZZNS
Status ID OPEN
Description New Starter

Not very exciting but it does the job. Now we need a business rule that is going to send an email to the new starter’s supervisor (a new foreign key in the Client module referencing the Client module called Seq.Supervisor) and create a new work order for the SYSTEM ADMINS to create an Active Directory and Exchange account.

HD01 – New Starter Ticket Stage 01

Conditions

when a Incident

Create occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
{TR,Subject ID} Equals ZZNS None

Actions

Action 1

Method Module Form
Create Notifications Notifications
Field Name Value
Subject Service Desk Notification: New Starter Approval Required
Body {TEMPL,HD14}
Notification Module Descr SMTP Email
Client ID {DB,Clients,Supervisor Client ID,”Sequence” = {TR,Seq.Client}}

Action 2

Method Module Form
Create Work Orders Work Orders
Field Name Value
Incident # {TR,Incident #}
Client ID {TR,Client ID}
Company ID {TR,Company ID}
Group Name SYSADMINS
Subject ID ZZNSEX
Status ID OPEN
Description Please create a new Active Directory / Exchange account for this new starter. Once complete, please update the client’s email address by clicking the Faces icon near the Client Information at the top of this form and entering the email address created. Thank you.
Resolution AD/Email Account Created and Client Successfully Updated.

Notification Business Rules Template HD14 referenced in the above business rule makes use of a two other templates created in my HTML Email Template post. Make sure you create the _HTML_HEAD and _HTML_FOOT templates otherwise this business rule wont work properly.

HD14 – New Starter Email To Supervisor

Template Name HD14
Template Data {TEMPL,_HTML_HEAD}
<P>Dear {DB,Clients,Supervisor First Name,”Sequence” = {TR,Seq.Client}},</P>
<P>
A new starter request has been raised for {TR,First Name} {TR,Last Name}. As the supervisor of this new starter you are required to specify which equipment configuration they will require. Please click on one of the links below and then send the email generated.
</P>
<H2>Eqiupment Selection</H2>
<TABLE>
{MATH,(SELECT _SMDBA_.GET_STD_CONFIGS({TR,Incident #}))}
</TABLE>
<P>
Thank you for your assistance with this service request.
</P>
{TEMPL,_HTML_FOOT}

That’s half of our business process complete. The email that is sent will automatically list all the Standard CI Assemblies available complete with hyperlinks such that when the supervisor clicks on their choice it will create an email configured in a way that we can pick up the inbound email and continue the business process. To pick up the inbound email from the supervisor we need a Mail Listen type business rule.

ML01 – New Starter Approval Received

Conditions

when a Mail Listen

Create occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
{TR,Subject} Contains Service Desk Notification: New Starter Approval Given None

Actions

Action 1

Method Module Form
Create Work Orders Work Orders
Field Name Value
Incident # {EXTRACT,^^,{TR,Subject}}
Client ID {DB,Incident,Client ID,”Incident #” = {EXTRACT,^^,{TR,Subject}} }
Company ID {DB,Incident,Company ID,”Incident #” = {EXTRACT,^^,{TR,Subject}} }
Group Name DESKTOPS
Subject ID ZZNSWS
Status ID OPEN
Description Please build a {DB,Standard CI Assembly,Description,”Name” = ‘{EXTRACT,[],{TR,Subject}}’} for the above user.

Additional Comments:
{TR,Message}

The above business rule simply creates the new workstation work order for our DESKTOPS team advising them of the choice our supervisor has made. The final business rule is the one that assigns the parent Service Request to the HR team for them to complete the new user induction. The trick here though is how do you know which work order is going to be completed first and how to you know when both work orders have been completed. The answer to the first is that you don’t (although you could take a good guess) and as such you need to allow for the fact that either of the work orders could be the last to be closed and should thus trigger this final business rule.

WO01 – New Starter WOs Completed

Conditions

when a Work Orders

Update occurs with

Expression 1 Comparison Operator Expression 2 Logical Operator
{DB,Incident,Subject ID,”Incident #” = {TR,Seq.HD #}} Equals ZZNS And
{MATH,(SELECT COUNT(*) FROM _SMDBA_._WORKORD_ WHERE STATUS = ‘O’ AND HDNUM = {TR,Seq.HD #})} Equals 0 And
{MATH,(SELECT COUNT(*) FROM _SMDBA_._WORKORD_ WHERE HDNUM = {TR,Seq.HD #})} Equals 2 None

Actions

Action 1

Method Module Form
Update Incident Incident (base)
Field Name Value
Incident # {TR,Seq.HD #}
Group Name HR
Description Please provide a new user induction for the above user.

So essentially what we are doing here is checking that the parent Incident / Service Request is a New Starter one and that the total number of linked Work Orders is 2 and the total number of open linked Work Orders is 0 i.e. they have all been closed.

Summary

Whilst this business process could have been a lot more complicated (I once implemented one with 6 parallel work orders etc.) this series of posts have shown how it is possible to chain business rules together to deliver a real business process. Hope this helps. As always, any feedback, positive or negative is always welcome. Thanks for reading.

Standard

Posted by

Posted on

December 31, 2009

Posted under

General

Comments

3 Comments

Season’s Greetings / Thank You

As 2009 draws to a close I thought I would just pen a final post for this year essentially to say “Season’s Greetings To All” and a massive thank you to all of you who have viewed, downloaded and commented (positively or negatively) on my content. It never ceases to amaze me how many people visit my site and, more importantly to me, how many different pages they view. The table below shows the high-level statistics:

  Visits Page Views Hits
Monthly Average 10,798 26,477 81,961
Yearly Total 129,582 317,725 983,541

To maintain (or hopefully increase) these numbers I need to keep writing / write more interesting articles and to do this I really need your help. What do you folk want me to write about? Are there any series that you would like to see? Let me know in the comments to this post and next year I will see what I can deliver.

Happy holidays everyone and thanks again.

Standard

Posted by

Posted on

November 8, 2009

Posted under

Service Desk Express

Comments

4 Comments

How To Restrict A Custom Self Service Module By Client

BMC Service Desk Express has awesome customisation tools that allow you create complete new tables (modules) for storing custom information in your Service Desk Express database. These custom modules can be presented in Self Service as well for end-users but here is the catch…

Out of the box you can’t restrict entries created by one client from another client.

Thankfully, with a little (well quite a lot actually) custom scripting it is possible. I should stress however, that if you are going to do this, MAKE SURE YOU BACK UP EVERYTHING (DB and Self-Service directories) FIRST!!!

Example

So let’s say that you want to create a new custom module to record feedback such that your clients can log Complaints, Compliments and Observations. The design of our custom module is as follows:

Table Name View Name Display Name
TBL_FEEDBACK Feedback Feedback
Alias Name Column Name Details
Sequence SEQUENCE INT, Primary Key, Created by Default
Last Modified LASTMODIFIED DATETIME, Created by Default
Last User LASTUSER STRING(30), Created by Default
Seq.Group _GROUP_ INT, Created by Default
InActive: _INACTIVE_: SMALLINT, Created by Default
Seq.Client SEQ_CLIENT INT, Foreign Key to Clients, NOT NULL
Feedback Type FEEDBACK_TYPE Validated Field (Compliment, Complaint, Observation), NOT NULL
Statement STATEMENT STRING(Unlimited), NOT NULL
Seq.Incident SEQ_INCIDENT INT, Foreign Key to Incidents, NULL
Status STATUS Validated Field (O,C), NOT NULL
Response RESPONSE STRING(Unlimited), NULL

We then create a form in for Self-Service, an appropriate popup, and modify the navigation bar such that the new custom module can be accessed as shown below:

Feedback Form 01

Issue

If Client ABARBER (that’s me by the way) submits a feedback record all appears to work perfectly. However, when SBARBER (that’s Sarah my wife) logs in, she can see my feedback and if she were to submit feedback, I would be able to see her’s as shown below:

Unfiltered Feedback 01

Solution

First run the following SQL command against your SDE DB and store the results somewhere:

SELECT BVD.TBLSEQ, COLSEQ, VIEWCOLNAME FROM dbo.SMSYSBASEVIEWDATA BVD JOIN dbo.SMSYSTABLES T ON T.TBLSEQ =
BVD.TBLSEQ WHERE T.VIEWNAME = ‘Feedback’ ORDER BY COLSEQ

My result looks like the table shown below but yours will look different depending on what you named your fields and how many custom modules you already have:

TBLSEQ COLSEQ VIEWCOLNAME
1002 1001 Sequence
1002 1002 Last Modified
1002 1003 Last User
1002 1004 Seq.Group
1002 1005 InActive:
1002 1006 Seq.Client
1002 1007 First Name
1002 1008 Last Name
1002 1009 Email Address
1002 1010 Feedback Type
1002 1011 Statement
1002 1012 Seq.Incident
1002 1013 Status
1002 1014 Response

Download RestrictingCustomSelfServiceModuleByClient.zip containing the files that need to replace the existing BMC files.

In the recurring_hd.asp and recurring_hd.htm files you need to do a find/replace replacing:

  • All occurrences of JOATIT_TBLSEQ with the  TBLSEQ number returned in the table above (so in my case 1002).
  • All occurrences of JOATIT_SEQ_CLIENT with the  SEQ_CLIENT number returned in the table above (so in my case 1006).
  • All occurrences of JOATIT_STATUS with the  STATUS number returned in the table above (so in my case 1013).

In the 1_common_label_message.js file you need to scroll right down the bottom and replace the “My Whatever Your Custom Module Is Called” with in my case “My Feedback”

Save the files and use them to overwrite the existing files in their appropriate directories.

Run the following script against your SDE DB…

INSERT INTO dbo.NAMSYSCSMESSAGES ([SEQUENCE], [LANGSEQ], [MESGNO], [MESGTYPE], [MESG]) VALUES (3224, 1, 1597, 2, ‘My Feedback’)

…and IISRESET you application server.

Summary

Hope this helps. Remember to backup everything as it is very easy to make a typo. Also remember, as this is custom code you may well have to reapply it after every patch release from BMC. Who knows, perhaps Service Desk Express 10 will make this post obselete…

As always, comments (positive or negative) are always very welcome and very much appreciated. I do try and answer every single one.

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

September 27, 2009

Posted under

Service Desk Express

Comments

Leave a comment

New Starter/Leaver Process – Part 2

In my previous post (New Starter/Leaver Process – Part 1) I described, diagrammatically, a new starter business process that could be implemented in Service Desk Express as shown below:

New Starter

In this post we are going to lay the foundations to build this process (by populating some necessary data) and then in the final post in the series we will complete the business rules.

Categories/Support Subjects

We need to add a couple of Categories /Support Subjects as shown below:

Subject ID Description Parent Subject ID
ZZ zzBusinessRules
ZZNS New Starter Process ZZ
ZZNSEX AD/Exchange Account Request ZZNS
ZZNSWS Workstation Request ZZNS

Now obviously it goes without saying that you don’t have to have the same categories as me. The reason I name Business Rules as zzBusinessRules is to shove them down the bottom of the Category Tree out of the way.

Groups

In my example I am going to have a centralised System Admin Group (SYSADMINS), Desktop Team (DESKTOPS), and HR Team (HR) who will do my induction process. Again, if you are following this you can call your groups whatever you want

CI Types/Inventory Catalogue Items

Whenever I write these posts I am always keen to make the example something that has a reasonable degree of complexity about it. Many technical examples are based around the equivalent of the Hello World application, and from bitter experience this CAN be more frustrating than useful. So in my example here, I am going to assume that we are using the Asset Management modules as well. So we’ll need the following CITypes/Inventory Catalogue Items:

Part # Description
STDWS01 Standard Workstation
STDLT01 Standard Laptop
STDMB01 Standard Mobile Phone
STDBU01 Standard Operating System/Applications Build
STD3G01 Standard 3G Card

Standard Configurations

We are then going to create two standard configurations as shown below:

Name Description
STDST Standard Static Configuration
Consisting of…
STDWS01 Standard Workstation
STDBU01 Standard Operating System/Applications Build

and

Name Description
STDMB Standard Mobile Configuration
Consisting of…
STDLT01 Standard Laptop
STDMB01 Standard Mobile Phone
STDBU01 Standard Operating System/Applications Build
STD3G01 Standard 3G Card

Standard Configurations User Defined Function

As anyone will tell you, when, as a developer, you hardcode stuff you invariably come to regret it (unless you have already left the organisation that is). I want to be able to send my supervisors a dynamic list of Standard Configurations that he/she can select from such that if that business decides at a later date to have a Non-Standard Mobile Configuration we can cope with it without the need for any code changes.
Alas, out of the box Service Desk Express can’t send a list of anything to anyone. This is where we need a little SQL help in the form of a User-Defined Function (UDF). Below is a link to a function I want to use to send out part of my email which can either be run yourself against the SDE database or given to your SQL DBA to run against the SDE database:

Function GET_STD_CONFIGS

If you haven’t used user-defined functions in SDE then you might find this post helpful.

Summary

So that is all the preparation work done. Now all we have to do is build our business rules to use all this stuff and that is what I will document in the third and final post in this series.