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.

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.

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.

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.

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.

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.

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.