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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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:

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:

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:

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:

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:

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

FULL DB Schema for Service Desk Express 9.20

The colleagues I work with keep asking me for a database schema for Service Desk Express 9.20 as it makes writing reports a whole lot easier. Whilst I know that BMC have gone some way to resolve this with the inclusion of the schemas in the documentation, the problem with these are:

a) There all on different pages – don’t get me wrong, I understand why, but it limits the usefulness of them.

b) Far more importantly, I imagine that there are very few implementations that don’t involve the creation/modification of a table or column. Consequently, the database schema is immediately out of date. In fact, it is already out of date as it does not include the Integration Engine tables.

So enough whinging – what am doing about it. Well I have created a Visio 2003 DB diagram of the entire database that is valid for a standard install of Service Desk Express 9.20. Every foreign key is included (I hope) along with all the correct field types and sizes. It is available free of charge to download and use as you find helpful. My theory being that you can update it to include your new tables and fields and hopefuly it will please your database administrators too!

SDE DB Diagram With Segregation Foreign Keys

SDE DB Diagram Without DB Segregation Foreign Keys

It is quite possible I have made a mistake in this schema – if I have please post a comment and I will update it as soon as I have time. Many thanks, and thanks for reading.

Allowing Remote Assistance to accept the machine name as a parameter

In a previous post I talked about how the Program Builder functionality of Service Desk Express allowed me to launch the Microsoft Remote Assistance tool. Whilst this was neat, what I really needed was to be able to prefill the machine name on the remote assistance page with a machine name as shown below:

Remote Assistance Screen

The first thing we needed to do was to hack the Remote Assistance tool to accept a parameter. To do this you need to open

C:WINDOWSpchealthhelpctrVendorsCN=Microsoft Corporation,L=Redmond,S=Washington,C=USRemote AssistanceEscalationUnsolicitedUnSolicitedRCUI.htm

using, for example Notepad. Near the top of the file there is a function called OnLoad that looks like:

function onLoad()
{
;
;
try
{
setTimeout("idComputerName.focus()",250);
g_oSAFRemoteDesktopConnection = oSAFClassFactory.CreateObject_RemoteDesktopConnection();
}
catch(error)
{
FatalError( L_RCCTL_Text, error );
}
;
return;
}

All we need to do is add a couple of lines of Javascript at the top of this function that basically says, if the QueryString of the passed URL (the bit after the ? in a URL e.g. p=51 in the case of http://www.joatit.com/wordpress/?p=51 ) is not equal to the word “machinename”, prefill the machine name in the idComputerName textbox. So after editing, the function should look like:

function onLoad()
{
;
;
try
{
if (window.location.search.substring(1)!= "machinename")
{
idComputerName.value = window.location.search.substring(1);
}
setTimeout("idComputerName.focus()",250);
g_oSAFRemoteDesktopConnection = oSAFClassFactory.CreateObject_RemoteDesktopConnection();
}
catch(error)
{
FatalError( L_RCCTL_Text, error );
}
;
return;
}

Ok, so save that and now we are ready to launch it from Service Desk Express. So as per the previous post, log onto the Service Desk Express application server and run builder.exe that lives in C:Program FilesBMCService Desk ExpressTools. You should be greeted with a screen that looks like the example below:

Program Launcher

In the Filename textbox enter: RemoteAssistance.asp

In the Command line textbox enter:

C:WINDOWSPCHEALTHHELPCTRBinariesHelpCtr.exe -url hcp://CN=Microsoft%20Corporation,L=Redmond,S=Washington,C=US/ Remote%20Assistance/Escalation/Unsolicited/ UnsolicitedRCui.htm?machinename"

and click Build Launch File.

Now using Form Customisation you can add a Customised Toolbar Button of type link to, for example, the Machine Name field in the Inventory Item module, by specifying a URL of:

http://<SDEServerName>/SDE/RemoteAssistance.asp?machinename=$64$

So, the obvious question is, where does the $64$ come from? The answer lies by running the following SQL statement:

SELECT * FROM SMSYSDBINFO WHERE View_Name = 'Inventory Items'

If you find the Machine Name field in the ALIAS column and look across to the INTERNAL_COL_ID for that field, you will find 64.

The final “icing on the cake” comes by using the DB Admin Tool to expose that Machine Name field to the Incident module by adding a new virtual field (let’s say called ‘Asset Machine Name’) through the existing Seq.Inv.Item foreign key. Run the following SQL statement…

SELECT INTERNAL_COL_ID FROM SMSYSDBINFO WHERE View_Name = 'Incident' AND ALIAS = 'Asset Machine Name'

and then using Form Customisation you can add a Customised Toolbar Button of type link on the Incident form to that newly exposed field. Now you can launch the Remote Assistance tool from the incident form, populating the machine name direct from the selected asset!

Hope it helps – as always all comments very welcome!

Forward schedule of change (for Self-Service)

One of the first projects I ever did was a forward schedule of change (FSC) for self-service. The organisation I was working for wanted to be able to display the FSC from within self-service. As this functionality wasn’t supported by Service Desk Express, I created a web application that enabled it.

Since then I have had a number of queries about how it was done (as I posted the pictures of it in the MagicSolutions Yahoo Group) and thought that it would be fun to rewrite with a bit of a spruce up, a little more functionality and, very importantly, a simple installer. For anyone interested, you will be pleased to know it is complete.

As with all JOATIT projects, it is available free of charge (as long as you don’t try to sell the application or the code (or any part of it) to anyone else). Additionally, the source code is available for anyone who wants to customise/reuse.

It is available from the Projects zone and as always, feedback is very welcome.