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!!!


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
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


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


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


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:

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 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…


…and IISRESET you application server.


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.

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.


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:

@ChangeNo int
RETURNS nvarchar(4000)
DECLARE @ReturnString nvarchar(4000)
SET @ReturnString = ''
DECLARE @LoginID nvarchar(30)
OPEN cur
WHILE (@@fetch_status <> -1)
IF (@@fetch_status <> -2)
SELECT @ReturnString = @ReturnString + @LoginID + ';'
RETURN SUBSTRING(@ReturnString,1,LEN(@ReturnString)-1)

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


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


when a Change Request

Update occurs with

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


Action 1

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


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.

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.


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


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:


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


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.

New Starter/Leaver Process – Part 1

Synchronising with Active Directory is NOT the best approach

This is a deliberately argumentative statement that, throughout this series of posts, I want to explore and explain my reasoning behind. The other aim of this series is to provide a real-world example of a new starter/leaver process that can be implemented in Service Desk Express.

For many years architects have been pushing for Active Directory synchronisation from every enterprise application vendor with, in most cases, very good reasons. AD synchonisation provides the business with a single directory source that can be used for everything from authentication/authorisation services to location and reporting structure data depending on how well it is populated. No one, not even argumentative me, would argue that a single directory source is a bad thing, nor that single sign on wouldn’t be welcomed by every enterprise user. My argument is NOT with AD. My objection to AD/Service Desk synchronisation is from a business process perspective – namely I believe that the source of HR data (which is what is being synchronised here) is better suited to the HR/Payroll system within an enterprise rather than AD. I can’t imagine that there are many organisations who add their new employees to AD before they are added to payroll and, in my opinion, the system admins should be tasked properly with creating an AD/Exchange account such that OLA/SLAs can be managed and customer satisfaction monitored.  So what, in my humble opinion, is the correct solution:

Data Flow Solution

Data Flow Diagram

As you can see from the diagram above, my solution would be to put the Service Desk in the centre acting as the hub through which human business processes are transacted. The initial employee record is created in the HR/Payroll system which then creates a client/customer record in Service Desk Express (using the Integration Engine). This in turn kicks off the New Starter business process that includes, amongst other tasks, an action to create an AD/Exchange account for the new employee. By updating the WinUserID field in the Client record with the user’s AD domainusername, the link between AD and Service Desk Express is then made and Self-Service Single Sign On can be provided. When employee records are updated (either through a request through the Service Desk or directly with the HR/Payroll system) the employee details are updated in Service Desk Express and then in AD.

New Starter Business Process

I am not going to discuss the initial synchronisation with the HR/Payroll system as this was discussed in a previous post (Using the Integration Engine to Manage Clients) and there is an appropriate Integration Engine package that can be downloaded and tweaked for your requirements. The key point to mention is that of “datakeys:”

Data Keys Diagram

As you can see Service Desk Express holds the datakey for both the HR/Payroll system (EmployeeID –> CLIENT) and AD (sAMAccountName –> WINUSERID). This is crucial to the solution.

New Starter

In my example business process above, the employee is going to be provided with the following:

  • Workstation (either static or mobile) with a standard operating system/applications build.
  • Active Directory and Exchange account for the domain.
  • Induction.

The important point to note about my example process is that activities happen both in parallel and sequentially and I want to demonstrate how this can be accommodated within business rules in Service Desk Express.

In the next post I will walk through the creation of the New Starter Process in Service Desk Express based on the output of the Using the Integration Engine to Manage Clients post I mentioned earlier.

How To Filter The Assign To Grids in Service Desk Express

A post in the MagicSolutions forum posed a challenge that I thought would be fun to try and solve, namely how to filter the Assign To grids in Service Desk Express. Unlike other popups that are accessed from a popup icon on the form these popups are accessed by clicking on the Assign To menu item. As such there is no out of the box facility within the application to filter this list as there is with other popups (see How to dynamically filter a popup list in Service Desk Express).


This is an unsupported hack of the ASP.NET code

The first thing to do is determine how you want to filter the popup as there is usually little point hardcoding something if it can be dynamic. For my example, I created a new field in the Support Staff module called Hide In Popups as shown below:

Hide In Popups

Now this field will be granted a unique number by Service Desk Express and it is important that you find out what this number is using the SQL statement below:


In my case this returned a value of 1001.

To filter the popup we need to add some code in assignto_grid.aspx (located in C:Program FilesBMCService Desk ExpressApplication Server by default) just before the opening <HTML> tag:

if (ViewName == "18" && sMode == "STAFF")
AddWhereClause += " + ||1001|| = 0";
else if (ViewName == "18" && sMode == "MYGROUP")
AddWhereClause += " AND ||1001|| = 0";

Save the file and you should be good to go.

So how does this code work. Well the ViewName == “18” bit means that I only want to filter the Support Staff not the Groups (which incidentally would be ViewName == “13”). The sMode bit refers to which menu item this popup was called from e.g. Member of My Groups. The AddWhereClause bit simply says check if field 1001 (my new Hide in Popups field) is equal to 0. As to why the syntax is different for the two modes – PASS!


Anyway, seems to work. As always any feedback (positive or negative) is always welcome.

Services, Organisations, and Service Level Agreements – Part 2

In Part 1 of this series I discussed how to create the Services, Organisations and Service Level Agreements (SLAs) necessary for our example of an internal service desk servicing six departments with the Finance department requiring a tighter SLA on one of the services then any of the other departments during March each year.

In this post, I will cover SLA Criteria (such that we can finish our example); linking Services, Organisations and SLAs;  and of course how to turn SLA Management on in the first place including the biggest gotcha of all.

SLA Criteria

We said that we wanted to provide our Finance department with a tighter SLA during March each year. How do we go about doing that? Well the first thing we need to do is create the new Urgency/Priority and SLA that we want to offer as shown below:

24HR Priority 24HR SLA 01 24HR SLA 03 24HR SLA 04

Another Gotcha

We need to add a SLA Criteria that checks if the month the call was opened was March. Problem – SLA Criteria can only check the values of fields within the module they are configured for (i.e. in our case Incidents) and don’t allow for any expressions. Solution – Create a field to hold the month the call was opened and populate it on save using a CSBR as shown below:

Month Opened

Expression 1 Comparison Operator Expression 2
{TR,Month Opened} Equals {NULL}
Field Value

You MUST add the field to the forms used otherwise you will get an error – Input string not in correct format. It CAN be hidden though.

Now in order that this SLA is only offered during March we need to configure an SLA Criteria as shown below:

24HR SLA 02

Notice that this criteria only considers the March clause NOT the Finance department clause. That comes later when we link Services, Organisations and SLAs. You could include the Finance department clause but then you would need to keep changing you SLA if, for example, another department wanted the same SLA.

In this example, given all the hassle of creating a field and CSBR, you could choose an alternative solution of selecting the 24HR_MARCH SLA and configuring the Start and End dates to only include March. The problem here of course is that you need to create one for every year which is a little painful. What is more, it wouldn’t have given me the opportunity to explain SLA Criteria 🙂

Service Organisation SLA Links

Now there’s a mouthful! Service Organisation SLA Links (LINK) are what they say on the tin – the way to assign a SLA to a service consumed by an organisation. They are accessible from the Service, Organization, and SLA forms via their respective tabs. In our example above we want each department to use our Standard 48HR SLA for the service of Application Support. Now we could create a separate LINK for each Organisation but we don’t need to. If we open the Service form, find our Application Support service and click Add on the SLA/Org tab we can create a LINK as shown below:


What the record above says is “Offer this SLA whenever the Application Support Service is selected.” This is fine for what we want at the moment. Now what we need to do is create another LINK for the Finance Organisation to provide the 24HR SLA against the Application Support Service as shown below:


Thus your Application Support service should look like this:

Service With LINKS 01

Now, when anyone logs a ticket against the Application Support Service between April and February inclusive, the only option will be the 48HR SLA as shown below:

Select from SLAs 01

However, when a member of the Finance department log a ticket against the Application Support Service during March, a choice of either the 24HR SLA or the 48HR SLA will appear as shown below:

Select from SLAs 02

…assuming you have turned the functionality on!

Service Level Agreement Rules

Yes that’s right – out of the box the SLA functionality is NOT turned on. To enable Service Level Agreements you need to click on the Service Level Agreement Rules item in the Service Level Management tab. Then from the Enable SLAs window that appears check the box to enable SLAs as shown below:

Enable SLAs 01

So here is the kicker and this is absolutely WOEFUL!

You have to manually enable the SLA functionality for each group by logging in as that group and turning it on by checking the box as described above! Imagine what that is like on 126 Groups! Scripts to the rescue…


The above script will turn on SLA Management for every group!

One Final Issue!

SLA’s are NOT mandatory out of the box. What this means is that if one of your Service Desk Agents decides to close the Select From SLA popup that appears to select the SLA, no SLA is populated at all. If you want to enforce SLAs you MUST make your SLA ID field selectable (i.e. NOT readonly) and write a Client Side Business Rule (CSBR) as shown below:
When an Incident is Saved

Expression 1 Comparison Operator Expression 2
{TR,SLA ID} Equals {NULL}
Method Module Form
Display Message Incident Current Form
Exit Rule if ‘OK’ clicked Checked


Hopefully, throughout these two posts I have given you an idea of what the art of the possible might be using the SLA modules available with Service Desk Express. As always, comments, positive or negative, are always welcome.

Dundas Map for SQL Reporting Services 2005 – Dynamic Symbols

As some of you might know, I am a BIG BIG fan of Microsoft’s SQL Reporting Services 2005 and STILL (Arggh!) haven’t had a chance to play with SQL Reporting Services 2008! For awhile now, I have been wanting to take my reports to a new level (mainly targetted at producing an enhanced suits of the Service Desk Express reports) and as such I have been looking into some of the 3rd party add-ons that are available.

Recently I have been doing some experimentation with the demo version of the Dundas Map for SQL Reporting Services 2005 – specifically attempting to dynamically populate symbols (in my case Cities) based on dynamic latitudes and longitudes retrieved from a database. Whilst the Dundas Map Control is very cool, this simple task is NOT at all straightforward and consequently I thought explaining it would make a good post. So here goes.

The Basic Stuff

Once you have installed the Dundas Map Control you can drag it onto any report using the SQL Server Business Intelligence Development Studio. A nice Wizard appears…KILL IT!! Seriously, the Wizard is fine if you want to colour sections of the map based on the sum of values for that area (e.g. sales in Wales vs Scotland). For dynamic symbols it is completely useless as you MUST populate dynamic symbols in code (thankfully either C# or VB.NET)! So you should end up with something that looks like this:

Dundas Map Example 03

Right-click on the Map and select Properties from the popup menu and this is where you can configure what part of the world the map displays. So in my case I have selected the UK and dragged my Dundas Map Control to fill the area of my report as shown below:

Dundas Map Example 04

Now my database structure looks like this:

Dundas Map Example 01

In order to make this post realistic, I want my report to call a stored procedure to return the Cities for a given region (or all regions) and as such I need a stored procedure, a dataset of regions and a parameter that uses these regions as shown below:


SELECT -1 As [RegionID], 'All Regions' As [RegionName] UNION SELECT RegionID, RegionName FROM dbo.Regions ORDER BY 2

RegionID Parameter:

Dundas Map Example 06

StoredProcedure [dbo].[up_Cities_Select]:

CREATE PROC [dbo].[up_Cities_Select]
@RegionID int
SELECT R.RegionName,
FROM dbo.Cities C
JOIN dbo.Regions R
ON R.RegionID = C.RegionID
WHERE (R.RegionID = @RegionID OR @RegionID = -1)
ORDER BY C.Population DESC


So far so good. Now here is where things get a little crazy. Right-click on the Map and select View Code from the popup menu. This bring up the Code Editor Window where you choose your choice of language and select the Event you are interested in coding for. For the purpose of this demo I will select VB.NET and the event I am interested in is PostInitialize as shown below:


There are couple of gotchas that you REALLY need to be aware of before you start using this control:

  • You cannot access the DataSources from the code and as such everything (including the connectionstring to the database) needs to be provided in code!
  • You cannot access the DataSets from the code which means that you need to write all the SQL in the code. For this reason I cannot imagine a realistic example when you wouldn’t use a stored procedure as it makes the code SO much more legible as you will see.

Let me step you through my simple code:

The first thing we need to do is add a SymbolDataBindingRule to tell the MapControl which fields to use for the Latitude, Longitude and Name.

Dim _sbr As New SymbolDataBindingRule
With _sbr
.Name = "sbr"
.BindingField = "CityName"
.XCoordinateField = "Longitude"
.YCoordinateField = "Latitude"
End With

Now we need to set up a database connection which requires a connectionString. I can’t be doing with hardcoding a connectionString in .NET code as it makes deployment from test to production a nightmare. So, what you can do is create an additional parameter (hidden this time) that holds the connectionString as shown below:

Dundas Map Example 05

Now whilst we are on the subject of Parameters we need to make these Report Parameters available to the Code by clicking on the Code Parameters tab and creating our two new Code Parameters as shown below:

Dundas Map Example 02

OK, so now we can carry on with our code creating our database connection:

Dim _objConn As New System.Data.SqlClient.SqlConnection(codeParams("paramConnectionString"))

We then create a SQLCommand object that uses the above connection, and appropriate stored procedure from the database:

Dim _objCmd As New System.Data.SqlClient.SqlCommand
With _objCmd
.CommandText = "dbo.up_Cities_Select"
.CommandType = System.Data.CommandType.StoredProcedure
.Connection = _objConn
End With

And we pass our parameters to the stored procedure as appropriate:

_objCmd.Parameters.Add("@RegionID", System.Data.SqlDbType.Int)
_objCmd.Parameters("@RegionID").Value = codeParams("paramRegionID").ToString

We then fire up our connection:


And create a SQLDataAdapter to fill a datatable with the results of the stored procedure:

Dim _objDA As New System.Data.SqlClient.SqlDataAdapter(_objCmd)
Dim _dt As New System.Data.DataTable

We then bind the map object (mapObj) to the datatable.

mapObj.DataSource = _dt

And then finally we iterate through each of the symbols we have created setting their name:

Dim _i As Integer = 0
For Each _symbol As Symbol In mapObj.Symbols
_symbol.Text = _dt.Rows(_i).Item("CityName")
_i = _i + 1

Before closing the connection:


Compile the code and then run it and with a bit of luck it should end up looking like this (obviously subject to some data):

Dundas Map Example 08


Another tip (stolen from a post in Dundas Support Forums) is to wrap all your code in a Try Catch such that your errors write out to a file on the local machine. This makes debugging ALOT easier:

...code above...
Catch ex As Exception
System.IO.File.WriteAllText("C:MapRSExceptionText.txt", ex.Message & " - " & ex.StackTrace)
End Try


You can download a copy of my database and the example report here.

So, as I said at the beginning of the post – not straightforward at all! But with a little work this control adds a valuable component to anyone’s SQL Reporting Services toolbox. As always, any feedback (positive or negative) is always welcome.