Service Desk Express API – Part 1

The more I play with the Integration Engine that ships with Service Desk Express, the more excited I get about the possibilities of using it. By far the most exciting piece of functionality in it for me is the XML Initiator – essentially a method of accepting inbound pre formatted XML messages and inserting and/or updating resultant records – more on this later.

What the Integration Engine doesn’t provide you with however, is a method of selecting data. This means that, if you wanted to build an external application that interfaced with Service Desk Express, even if you made use of the Integration Engine to insert/update records, you would still need to access the database directly to view records.

What I propose to demonstrate in a series of posts is a method of creating an “adapter” or API for the Service Desk Express Integration Engine that allows developers in your organisation to interface with Service Desk Express through a single “portal”. That “portal” will be a web service that provides the building blocks to select insert or update any record (or group of records) without a single change to the database schema or application code. What is more, as the inserting and updating of records will be done via the Integration Engine (using the aforementioned XML initiator), business rules/logic will be completely supported.

Solution Overview

SDE API Overview

SDE API Overview

Essentially then, what we are going to build is an interface layer. Developers will be able to make a single reference to this interface layer and call “services” that, at their most basic level, allow them to retrieve a single string value or a dataset and, insert or update records without the need to understand how to invoke the XML Initiator functionality of the Integration Engine.

XML Message

So in this first part, I want to walkthrough the process of creating an Integration Engine package that is initiated by an xml message, and inserts/updates an Incident appropriately (depending on whether an Incident # is passed). I also want to explain some of the pitfalls I fell into when doing this and how to get around them!

To do that I need to explain VERY BRIEFLY what OUR xml message looks like that is going to cause this package to run. Hopefully, you will have noticed the capitalisation in the previous sentence – this is NOT a lesson in xml!

<?xml version="1.0"?>
<request>
<incidentSequence>3</incidentSequence>
<clientID>ABARBER</clientID>
<configurationID>S_01_B_03_R_332</configurationID>
<serviceName>DESKTOP</serviceName>
<supportSubjectID>HWCD</supportSubjectID>
<incidentDescription>My CD-ROM just exploded!</incidentDescription>
<inventoryItemSequence>34442</inventoryItemSequence>
<incidentResolution></incidentResolution>
<userDefinedStatusID>OPEN</userDefinedStatusID>
</request>

The first line is simply a declaration specifying the version of xml we are using. After that we have a single parent element called “request”. Technically, it doesn’t matter what you call this element but if you call it “request” you will have a lot less work to do in future parts, if you are following along. This parent element translates to a table in the Integration Engine. Inside this request element are a number of child elements. Each of these child elements (e.g. <clientID></clientID>) translates to a column inside the parent table. Between the opening element name (<clientID>) and the closing element name (</clientID>) is the value that is to be inserted into that column.

XML Initiator

So let’s build a package that accepts the above xml message as an initiator.

BTW, as with all my Integration Engine posts the packages are provided at the bottom of the post to be imported into your solution if appropriate. That said, I want to walkthrough the steps as hopefully it will assist in creating different or more complicated packages/integrations.

We begin by logging into the Integration Engine (http://appservername/integrationconsole by default) and creating a new package called “Incident_InsertUpdate”. Click the <Package Editor> button.

Incident_InsertUpdate 01

Incident_InsertUpdate 01

As we are only going to use a single step I have used the same name, Incident_InsertUpdate, for the step name. Click the <Step Editor> button.

Incident InsertUpdate 02

Incident InsertUpdate 02

Select the XML Initiator Type and click the XML Source tab.

Incident InsertUpdate 03

Incident InsertUpdate 03

In the Enter URL text box enter Incident_InsertUpdate.post. Enter XmlData as the Post Variable Name and paste our xml message above into the Sample XML Data removing, I would suggest, my sample data!

Before we carry on let’s slow down and understand what this screen is about. In IIS, under your Service Desk Express website are a bunch of virtual directories as shown below (don’t worry if you don’t have this many as there are a few of my own):

Incident InsertUpdate 06

Incident InsertUpdate 06

Critically, there is one called XMLPostHandler. The XML Initiator monitors this virtual directory for post files (*.post) and when it receives one, it checks its list of packages for a matching name – in our case Incident_InsertUpdate.post. That is what you are specifying in the Enter URL text box and it is this that allows the Integration Engine to know which package to invoke based on which xml message.

The Post Variable Name is the variable name in the file that contains the xml message. Don’t worry too much about this as we are going to handle this in a latter post so developers don’t have to.

The Sample XML Data simply allows you to complete the rest of the package with a certain amount of intellisense such that in latter steps your fields are available for selection.

Click the Select Table tab.

Incident InsertIpdate 04

Incident InsertIpdate 04

Notice that the Select Table Name is prefilled with the word “request”. This is parent element name from our sample xml file. Click the Data Fields tab.

Incident InsertIpdate 05

Incident InsertIpdate 05

That’s the Initiator configured. Click the Source question mark.

Incident InsertUpdate 07

Incident InsertUpdate 07

This one is really easy. Select None/Not Used as the Adapter Type. This is because the Initiator is the source as well as the initiator. Click the Target question mark.

Incident InsertUpdate 08

Incident InsertUpdate 08

Select SDE as the Adapter Type and click the SDE Connection tab.

Incident InsertUpdate 09

Incident InsertUpdate 09

Select SDE as the DSN, enter any account that has read/write access to the SDE database in the User ID textbox and enter it’s password in the Password textbox. Enter your _SMSYSADMIN_ password in the _SMSYSADMIN_ Password textbox. Click the SDE Details tab. If you got it correct then this will populate – if not you’ll get an error here.

Incident InsertUpdate 10

Incident InsertUpdate 10

Select SYSTEM ADMINISTRATION as the Group Name, Incident as the Module Name, and Insert/Update and the Insert/Update Mode. Check the box to Use Business Rules. Click the SDE Sub Details.

Incident InsertUpdate 11

Incident InsertUpdate 11

Given that we are updating as well as inserting, the Integration Engine needs to know the unique key to update based on – in this case Incident #. Click the Data Field tab.

Incident InsertUpdate 12

Incident InsertUpdate 12

That’s the Target specified. Now click the Mapping icon to finish off.

Incident InsertUpdate 13

Incident InsertUpdate 13

Go through the fields highlighting the target and the source columns and clicking Quick Map to enter them such that for example, Incident # in the target column matches incidentSequence in the source column. You should end up with something that looks like the above. Click the Save button.

Now here is where the wheels first fell off the bus so to speak. If you leave the package like that it will work BUT (and it is a pretty big but) only if all the fields are populated every time. As we plan on handling inserts (when we don’t know what the Incident # will be) this is pretty useless. Don’t panic – help is at hand. Click the Advanced tab!

Incident InsertUpdate 14

Incident InsertUpdate 14

What you are looking at is what you just created but in VBScript as opposed to a nice GUI. We are going to edit this VBScript! Click the Edit Script checkbox.

Replace the mapData() sub provided with the code below:

Sub mapData()
'targetRow("TargetColumn") = sourceRow("SourceColumn")
If (initiatorSourceRow("incidentSequence") <> "") Then targetRow("Incident #") = initiatorSourceRow("incidentSequence")
If (initiatorSourceRow("clientID") <> "") Then targetRow("Client ID") = initiatorSourceRow("clientID")
If (initiatorSourceRow("configurationID") <> "") Then targetRow("CI Assembly ID") = initiatorSourceRow("configurationID")
If (initiatorSourceRow("serviceName") <> "") Then targetRow("Service Name") = initiatorSourceRow("serviceName")
If (initiatorSourceRow("supportSubjectID") <> "") Then targetRow("Subject ID") = initiatorSourceRow("supportSubjectID")
targetRow("Incident Description") = initiatorSourceRow("incidentDescription")
If (initiatorSourceRow("inventoryItemSequence") <> "") Then targetRow("Seq.Configuration Item") = initiatorSourceRow("inventoryItemSequence")
targetRow("Incident Resolution") = initiatorSourceRow("incidentResolution")
If (initiatorSourceRow("userDefinedStatusID") <> "") Then targetRow("Status ID:") = initiatorSourceRow("userDefinedStatusID")
End Sub

Don’t panic! All we are doing is telling the Integration Engine, “Don’t both doing a mapping if the source data is blank.” Hence the bunch of VBScript If statements.

Click the Save button and your done.

Now the cool thing here is that you can test this package WITHOUT a client. All you need to do is go back to the Initiator section and click the XML Source tab. If you specify valid dummy data in the Sample XML Data (e.g. <clientID>ABARBER</clientID> as opposed to <clientID></clientID>) then you can go ahead and click the Execute Package button and the package will execute using your valid dummy data. If for some reason it doesn’t work take a look at my previous “Debugging Integration Engine Packages” post for help or drop me a mail (alan@14j.co.uk).

Integration Console Package: Incident Insert/Update Integration Console Package

So that’s it for this post. Feedback always welcome. In a future post we’ll take a look at the elements of our interface layer and build on what we’ve done in this post.

Change Management Business Process

They always say,

“…a picture paints a thousand words…”

and, whilst generally not considered works of art, I find swim lane diagrams or cross-functional flowcharts (as they are called in Microsoft Visio) are a really superb way of explaining who does what when.

So, in the spirit of the above, whilst bored this evening (sat in a hotel room on my M2576 Implementing and Administering Microsoft Internet Information Services (IIS) 6.0 training course), I thought I would put together an example business process diagram for Change Management:

Change Management - Creation, categorisation and assessment

Change Management - Creation, categorisation and assessment

Change Management - Approval, release and closure

Change Management - Approval, release, and closure

I have saved it as both a Microsoft Visio 2003/2007 and Microsoft Visio 2002 diagram such that it might be useful to you as a starting point for your own Change Management process diagrams:

Microsoft Visio 2003/2007 Download
Microsoft Visio 2002 Download

As always, comments/feedback (positive or negative) is always welcome.

Automatically closing resolved calls after x days

Ok, so let’s say that you’ve implemented ITIL in your organisation and one of the requirements is that incidents are RESOLVED by the Service Desk and then CLOSED by the client. This is easy enough to implement in Service Desk Express by just creating a new User-Defined Status of RESOLVED that is flagged to “Stop The Clock” as shown below. (Incidentally, you need to create a custom version of the User-Defined Status form to add the “Stop The Clock” tick box to it.)

New Resolved Status

New Resolved Status

The issue however, is that your clients are not closing their calls and consequently management want the RESOLVED calls to be CLOSED automatically after 2 days of inactivity. This is possible to do with business rules BUT it’s horrible because your job queue fills up with all the tickets that are awaiting closure and as such, identifying failed rules becomes a nightmare. So, an alternative solution.

If you are using Service Desk Express 9.1 or above then salvation is here with the introduction of the Integration Engine. In this post I am going to use the Integration Engine to build a package that will do what we’ve mentioned above:

So we fire up the Integration Engine (http://appservername/integrationconsole by default) and add a new package called Close Resolved Calls. We then create a single step called the same thing as shown below:

Closing Resolved Calls 01

Closing Resolved Calls 01

When then customise the step as follows:

Initiator:

We use the Scheduler initiator as we want this function to run in the background without human intervention. In this example I have set it to run every hour starting at midnight.

Closing Resolved Calls 02

Closing Resolved Calls 02

Source:

Now this is where it gets interesting as we need to specify a query that is going to get us all calls that are RESOLVED (I’ve added this as a User-Defined Status), which means they are open at the moment, that haven’t had any activity in the last two days. I am defining inactivity here as no Incident Details records created. The query to do this is as follows:

SELECT I.[Incident #] FROM [_SMDBA_].[Incident] I WHERE (SELECT Top 1 DATEDIFF(dd, ID.[Date], GETDATE()) FROM [_SMDBA_].[Incident Details] ID WHERE ID.[Incident #] = I.[Incident #] ORDER BY ID.[Date] DESC) > 2 AND I.[Status ID:] = 'RESOLVED' ORDER BY I.[Incident #]

As we want to run a specific query we need to use an ODBC source as opposed to SDE (or at least I find it easier than using the filter option).

Closing Resolved Calls 03Closing Resolved Calls 04Closing Resolved Calls 05Closing Resolved Calls 06

Target:

So now we have a source of all the incidents that are open, with a status of RESOLVED, and no incident details in the last 2 days. Now we specify the Target which in this case is the Incident table in Service Desk Express. Notice how the Insert/Update method is set to Update only, the business rules are being used, and we are updating on Incident #.

Closing Resolved Calls 07Closing Resolved Calls 08Closing Resolved Calls 09Closing Resolved Calls 10

Closing Resolved Calls 11

Closing Resolved Calls 11

Mapping:

So finally, all we need to do is map the [Incident #] together and hard-code the [Status ID:] = ‘CLOSED’.

Closing Resolved Calls 12

Closing Resolved Calls 12

And that’s it. Every hour it will pick up any calls that meet the criteria (incidents that are open, with a status of RESOLVED, and no incident details in the last 2 days) and it will close them. If you have a business rule that fires on update of an incident to a status of CLOSED then this will automatically fire as well. This is another huge plus of the Integration Engine.

You can download the final package here.

As always, keep the feedback coming (whether it be positive or negative). Hope it was useful.

TechMagic

Another project released. TechMagic is designed to provide engineers with a license free way of viewing incidents, work orders, purchase requests, problems and change requests assigned to themselves or a member of a group they belong to held in the BMC Service Desk Express database.

As with all my projects they are completely free. Any feedback would, as always, be greatly appreciated.

Using the Integration Engine to Manage Clients

The Integration Engine that ships with Service Desk Express is really rather cool and building an integration package with it is really very easy – or so I thought.

What I wanted to achieve was a way synchronising my client records with a master HR datasource like SAP or Peoplesoft including identifying who the client’s line managers were and their appropriate departments – all without the source HR system having to provide me anything other than a single view of active clients. Easy I thought. Wrong! It was actually a lot more difficult than I had imagined and as such, I thought it might be helpful to create a little demo to a) highlight some of the complexities and b) show you how to get round them.

So before we begin – a little background. For the purposes of this demo I have recreated an HR database that has a very simple schema as shown below:

HR Schema

HR Schema

The view (vw_Active_Employees) from the HR database ONLY shows active clients . This is important as the last thing your network team want is to pull all employees (both active and inactive) each night as over time this will grow to a potentially massive list.

So I built an integration package initially with one step: Synchronise Clients as shown below:

Synchronise Clients 01Synchronise Clients 02Synchronise Clients 03Synchronise Clients 03Synchronise Clients 03Synchronise Clients 03Synchronise Clients 03Synchronise Clients 03Synchronise Clients 03Synchronise Clients 03Synchronise Clients 11

So this all works perfectly and I am able to change people’s details in the HR system e.g. email address and have that synchronise in Service Desk Express. I am also able to add new users and these appear in Service Desk Express.

The first problem comes when I make an existing user in my HR database InActive. Because they no longer appear in the view (remember the view from HR only includes active employees), Service Desk Express doesn’t do anything with them. The obvious solution is to make all the client records in Service Desk Express InActive first and then update them to active IF they appear in the HR view. So how do you do this using the Integration Engine?

It’s actually quite easy but takes a little getting you head around. You need to create a new step that runs before the Synchronise Clients step that has a source and target of Service Desk Express. The source data comes from a simple script:

SELECT [Sequence] FROM [_SMDBA_].[Clients] ORDER BY [SEQUENCE]

The target is then the Clients module and you simply map Sequence to Sequence and hardcode InActive:= 1 as shown below:

InActivate Clients 01InActivate Clients 02InActivate Clients 03InActivate Clients 04InActivate Clients 05InActivate Clients 06InActivate Clients 07InActivate Clients 08InActivate Clients 09InActivate Clients 10

Success! Almost. That sorts out the “Leavers” and automatically inactivates those people who are not in the view anymore. The next problem is the departments – what happens if HR adds a new department or renames one or inactivates one?

This one is easy to solve as well in that all you do is create two new steps BEFORE the two we already have:

  • InActivate All Departments
  • Synchronise Departments

Suffice to say I wont produce screen shots for every step of this process but the important ones are the source data and the mapping:

Synchronise Departments 01Synchronise Departments 02

Notice the SQL statement in the source:

SELECT DISTINCT [DepartmentID], [DepartmentName] FROM [dbo].[vw_Active_Employees] ORDER BY [DepartmentID]

All we are doing here (and it may not be appropriate for your organisation) is synchronising ONLY those departments where there is an active employee. All other departments, as part of the previous step, get inactivated.

Ok, so we are almost there. The final problem was how to handle the SupervisorIDs. The issue here is simply one of timing. The view from HR could have an employee whose SupervisorID is a new starter and consequently an error will occur as the SupervisorID is not in Service Desk Express before trying to be referenced. So how do we fix this issue, which incidentally would be the same for any import where there is a self-referencing foreign key relationship?

All we need to do is amend the Synchronise Clients step such that we don’t map SupervisorID to Supervisor Client ID as shown below:

Synchronise Clients 11 Amended

That will allow all the employee/clients to be synchronised without updating their SupervisorID’s. Once that step is complete and a success we simply add one final step which is a copy of the Synchronise Clients step except that it just updates the SupervisorID’s as shown below:

Synchronise Supervisors 01Synchronise Supervisors 02

You can download the final package here.

And that’s it. I hope this has been of help – as always comments, both positive and negative, are always welcome.

In/Out Board

Thought this little “ditty” might be of use to someone…As Magic/SDE Administrators you are trying to persuade your users to enter their User Availability data and keep it up to date. Whilst this information is useful for the routing of calls etc. it is not displayed anywhere and consequently is only useful when actually assigning calls to users. It might be beneficial to be able to see, at a glance, all the users and whether (according to the user’s User Availability) they are in or out.

The key to doing this lies in joining the SMSYSUSERDATA and _PERSONNEL_ tables together. What I’ll demonstrate in this post is two ways in which this data can be used:

Report

So the easy solution is to build a report – an IN/OUT board if you will, that will show the status of all members of staff. The SQL for this is:

SELECT P.[CODE], P.[NAME] + ', ' + P.[FNAME] As 'Name', CASE WHEN GETDATE() BETWEEN A.[NOTAVAILABLESTART] AND A.[NOTAVAILABLEEND] THEN 'Out' ELSE 'In' END As 'In/Out'
FROM _SMDBA_._PERSONNEL_ P JOIN dbo.SMSYSUSERDATA A ON A.[SEQUENCE] = P.[SEQUENCE] WHERE P.[_INACTIVE_:] = 0 ORDER BY P.[NAME]

Quite obviously you can change the query to your hearts content to return further fields e.g. phone number etc. You could also add an optional parameter to be able to search for a specific user as well.

This solution is really easy to do but has the obvious limitation that the user’s need to run the report for it to be of any real use.

Calculated Field

So the alternative is to create a calculated field in the Support Staff module with the following SQL:

(SELECT CASE WHEN GETDATE() BETWEEN "NOTAVAILABLESTART" AND "NOTAVAILABLEEND" THEN 'Out' ELSE 'In' END FROM "SMSYSUSERDATA" WHERE "SEQUENCE" = "BASE"."SEQUENCE")

Now, if you then create a Quick View based on the Support Staff module you can add this field in it and even colour code based on whether the user is in or out.

In Out 01

In Out 01

 
In Out 02

In Out 02

As always, comments (positive and negative) are always welcome. Thanks for reading.

Traffic Lights – Calls On Hold

A while back I posted an article on how to implement the traffic light concept in Service Desk Express (http://www.joatit.com/wordpress/?p=69). When I implemented this for our contracts the immediate response was “Great but…Can you make the tickets on hold go a different colour?”

Isn’t it nice to be able to say “Of course!” Here is how:

(CASE WHEN (SELECT "STOPTHECLOCK" FROM "_SMDBA_"."_UDSTATUS_" WHERE "SEQUENCE" = "BASE"."SEQ_UDSTATUS:") = 1 THEN 'On Hold' WHEN GETDATE() > "RECOMMENDEDFIX_DATE:" AND GETDATE() < "DUE_DATE:" THEN 'Warning' WHEN GETDATE() > "DUE_DATE:" THEN 'Critical' ELSE 'Normal' END)

Hope it helps. As always, please keep the feedback coming. It makes sure that what I am posting is of relevance and interest to the people reading.