Service Desk Express API – Part 2

In my first post in this series (Service Desk Express API – Part 1) I discussed the XML Initiator and demonstrated how to build an Integration Engine package that would accept an inbound XML message and raise an incident. In this post I want to take this a step further and discuss how to build the XML message (in C#) to send to the Integration Engine. To do this I have built a fairly simple web service that will provide our development interface to Service Desk Express, which during this post I will explain how it can be configured to provide any function. You can download the web service here.

You will need to create a new virtual directory in IIS called APIMagic pointing at the contents of the APIMagic directory in this zip file, with “Scripts Only” execute permissions and configured to use the .NET Framework 2.0 as shown in the screen shots below:

Before we start configuring our functions (or webmethods) that our developers will use we need to configure the web service to talk to Service Desk Express.

Configuration Settings

As discussed in the first post in this series, one of the objectives we are trying to achieve is to provide a way of retrieving data from the Service Desk Express database. To do that we need to provide details of how to connect to the database in the form of a connectionString entered in the web.config. Open the web.config file and modify the section shown below to point to your DB server using whatever SQL username and password you like:

<connectionStrings>
<add name="SDE" connectionString="Data Source=DBServerName;Initial Catalog=SDE;User ID=APIMagic;Password=APIMagicPassword;" providerName="System.Data.SqlClient" />
</connectionStrings>

The account shown here APIMagic only needs db_reader permissions to the Service Desk Express database.

The other piece of information we need to provide is where the XmlPostHandler service is located. As discussed in the first post in this series this is normally: http://appServerName/XmlPostHandler and this value needs to be entered in web.config as an applicationSetting as shown below:

<appSettings>
<add key="XmlPostHandlerURL" value="http://appServerName/XmlPostHandler/" />
</appSettings>

So once you have set these two configuration settings, save the file and you are ready to start building functions…

Before we do however, I wanted to just explain a couple of features of the webservice and how it works.

Structure and Generic Functions

Essentially, the most important thing to realise is that your developers will connect to the Service.asmx file which will in turn provide access to the Service.cs file in the App_Code directory. All of your custom functions will be written in the Service.cs file and this is the only file (other than the web.config file we modified earlier) that you will need to work with.

Supporting the Service.cs file is a number of other files (or classes): Data_Access.cs, Utility.cs, and XML_Parameter.cs. You don’t need to worry about these files but you do need to understand the generic functions that are available to you and how to use them.

Data Access Class

The Data_Access class handles all the “complex” stuff with regards retrieving data from the Service Desk Express database and building our XML documents and sending them to the Integration Engine for parsing.

Essentially it provides three functions:

  • Return_Single_Value: This function will return a single string of data from the database as the result of a Select statement, e.g. SELECT CLIENT FROM _SMDBA_._CUSTOMER_ WHERE SEQUENCE = 1001.
  • Return_DataSet: This function will return a dataset of data (essentially a table) from the database as the result of a Select statement, e.g. SELECT * FROM _SMDBA_._CUSTOMER_
  • PostXML: This function, in conjunction with the XML_Parameter class will allow you to create and send XML messages or posts to the Integration Engine for processing.

XML Parameter Class

Fundamentally, the XML Initiator in the Integration Engine parses the nodes of the XML document posted to it. The function of the XML_Parameter class is to provide a reusable business object that allows us to define those XML nodes for any given record (e.g. an Incident).

The class has three properties: Name, Value, and Parent. To explain these properties lets consider a node from our example XML document shown in the first post in this series.

<request>
<clientID>ABARBER</clientID>

</request>

So to build this node our properties would be:

  • Name: clientID
  • Value: ABARBER
  • Parent: request

So for each node of our XML document we will instantiate a new instance of the XML_Parameter class and pass in the appropriate values. More on this later so don’t panic.

So let’s build something useful

So, by way of an example, we will create three functions to demonstrate how the service is used:

  • Incidents_SelectCountByClientID: A function that simply returns the number of Open/Closed/Both incidents for a given Client ID.
  • Incidents_SelectByClientID: A function that returns a dataset of incidents for a given Client ID.
  • Incident_InsertUpdate: A function that creates/updates an incident.

These three functions are the same three functions that are already coded in the Service.cs file and commented out.

Incidents_SelectCountByClientID

The purpose of this function is to demonstrate the Return_Single_Value function described earlier. I doesn’t really matter what the SQL query is as long as it returns a single value.

[WebMethod(Description="Function to return the count of open/closed/both incidents for a given Client ID.")]
public string Incidents_SelectCountByClientID(string sClientID, string sState)
{
try
{
string _Value = "";
string _SQL = "SELECT Count(*) FROM [_SMDBA_].[Incident] WHERE [Client ID] = '" + sClientID + "' AND [InActive:] = 0";
if (sState.ToUpper() != "B")
{
_SQL = _SQL + " AND [State:] = '" + sState + "'";
}
_Value = _da.Return_Single_Value(_SQL);
return _Value;
}
catch (Exception ex)
{
throw _u.Error_Handler("Return_Single_Value", ex);
}
}

As you can hopefully see, it wouldn’t be that complicated to change the function or create a new one for selecting the number of work orders or purchase requests for a given Client ID. All you need to do is copy and paste the above code and change the _SQL statement.

Incidents_SelectByClientID

The purpose of this function is to demonstrate the Return_DataSet function described earlier.

[WebMethod(Description = "A function that returns a dataset of incidents for a given Client ID.")]
public DataSet Incidents_SelectByClientID(string sClientID)
{
try
{
DataSet _ds = new DataSet("Results");
string _SQL = "SELECT * FROM [_SMDBA_].[Incident] WHERE [InActive:] = 0 AND [Client ID] = '" + sClientID + "'";
_ds = _da.Return_DataSet(_SQL);
return _ds;
}
catch (Exception ex)
{
throw _u.Error_Handler("Return_DataSet", ex);
}
}

Again, if we wanted a function to return all Work Orders we would simply copy and paste this function, change it’s name, and change the _SQL string to select from work orders instead.

Incident_InsertUpdate

The purpose of this function is to demonstrate the PostXML function described earlier, and for me, this is the most exciting of the functions. To post any XML file to the Integration Console all you need to do is create a list of XML Parameters and call this function as shown below:

[WebMethod(Description = "Function to either update or insert an incident record into the SDE database.")]
public bool Incident_InsertUpdate(string iIncidentNo, string sClientID, string sConfigurationID, string sServiceName,
string sSupportSubjectID, string sIncidentDescription, string iInventoryItemSequence,
string sIncidentResolution, string sUserDefinedStatusID)
{
bool _isSuccess = false;
try
{
List<XML_Parameter> _xmlParameterCollection = new List<XML_Parameter>();
_xmlParameterCollection.Add(new XML_Parameter("incidentSequence", iIncidentNo, "request"));
_xmlParameterCollection.Add(new XML_Parameter("clientID", sClientID, "request"));
_xmlParameterCollection.Add(new XML_Parameter("configurationID", sConfigurationID, "request"));
_xmlParameterCollection.Add(new XML_Parameter("serviceName", sServiceName, "request"));
_xmlParameterCollection.Add(new XML_Parameter("supportSubjectID", sSupportSubjectID, "request"));
_xmlParameterCollection.Add(new XML_Parameter("incidentDescription", sIncidentDescription, "request"));
_xmlParameterCollection.Add(new XML_Parameter("inventoryItemSequence", iInventoryItemSequence, "request"));
_xmlParameterCollection.Add(new XML_Parameter("incidentResolution", sIncidentResolution, "request"));
_xmlParameterCollection.Add(new XML_Parameter("userDefinedStatusID", sUserDefinedStatusID, "request"));
_da.Post_XML_Request("Incident_InsertUpdate.post", _xmlParameterCollection);
_isSuccess = true;
return _isSuccess;
}
catch (Exception ex)
{
throw _u.Error_Handler("Incident_InsertUpdate", ex);
}
}

Let’s take a look at how this function works. Essentially all that is happening is that for each variable that is being passed to the function (i.e. sIncidentNo, sClientID etc.), a new instance of the XML_Parameter class is being instantiated and passed the appropriate values to create an XML node. The parent node is called “request” hence why, at least in my example, all the nodes are being passed the value “request” as their parent value.

So what would happen if you wanted to pass a new variable. Well you would simply create a new variable in the function declaration and then add another XML Parameter:

e.g. _xmlParameterCollection.Add(new XML_Parameter("problemNo", sProblemNo, "request"));

What about if you wanted to create/update work orders instead. Assuming that you have already created a new Integration Engine package, copy and paste the function, change it’s name and, and this is the important bit, change the post value to something sensible that you have specified in your Integration Engine package:

e.g. _da.Post_XML_Request("WorkOrder_InsertUpdate.post", _xmlParameterCollection);

Testing

To test any of the three function you have built (or uncommented perhaps :-)) simply open a browser (ON THE SERVER WHERE THE WEBSERVICE IS INSTALLED) and type http://localhost/APIMagic/Service.asmx and you’ll be greeted with the Service.asmx webpage as shown below:

Click on any of the links and enter appropriate values and you should find it all works as shown below:

Summary

It may not be much at the moment but, simply but copying and pasting the WebMethods and changing a small amount of code (obviously coupled with some appropriate Integration Engine packages) you should be in a position to provide a genuine API to Service Desk Express.

The truth is, I have found this post very hard to write as I didn’t quite know what to explain in detail and what to glance over. Between now and when BMC decide to release their new Web Services module for Service Desk Express, I will keep updating this web service with new functions. I would really welcome any feedback a) in general and/or b) specific to this post/project whether positive or negative. I hope this has proved useful.

Advertisements

16 thoughts on “Service Desk Express API – Part 2

  1. Hi Alan,

    Thank you for posting this information. I’m just getting started on an SDE integration project and so far have not found anything nearly as useful in the actual BMC documentation.

    So, thanks!!! This will save us lots of time.

    Take care.

  2. Hi Bob,

    Glad the information is helpful and relevant. If you need any help with you integration project let me know. In Service Desk Express 9.8 BMC have introduced support for Web Services which has some significant advantages. Intend to do a new post about this after the Christmas break.

    Best regards,

    Alan

  3. Hi Alan,

    I require some help as to how i can have multiple SDE db’s on a single server. Second, i have a scenario where by my organization is having a SDE installation currently, we are using change mgt here extensively, we have a Managed Services provider who we want to include in our change cycle. They too have their setup of SDE where in they manage different clients. Now the question is how can we create a assessment/approval for them and have them update it in real time in our sde db. This change inof needs to be inserted into their db too.
    Do u have any idea as to how we can go abt this.
    I’d appreciate any feedback from u.

    thanks.

    A.Robinson

  4. Hi Ashit,

    You can have two SDE dbs on the same server but unless you have two instances of SQL Server installed with one SDE db in each then all the passwords need to be exactly the same as you can duplicate the SQL accounts. A separate instance is a much better way to go.

    Re the integration project that should be very doable. What you want to start with is a process flow diagram (similar to http://www.joatit.com/wordpress/?p=271) and then go from there. Your options will depend of what version of SDE you are both using and what connectivity/firewall issues you have between you.

    Drop me an email (alan@14j.co.uk) with some details and I’ll gladly take a look.

    Regards,

    Alan

  5. This is a great case study !

    My question is quite simple:
    When I create a ticket, I would like to receive in return the incident Id in a XML data structure…as a response to my XML post request.
    As I understand the code, I will only receive an HTTP code 200 OK telling me that the XML form has been posted successfully. Am I right ?

    I suppose that I should run a “select” query on the DB to select the “latest” incident Id created by the Client before returning from the method Incident_InsertUpdate. Am I right ? Is there a better alternative ?

    Thanks a lot,

    Luc

  6. Hi Luc,

    Yes you are absolutely correct. You could attempt to get the most recent record but I would advise against it particularly in a high volume environment.

    The best alternative is to upgrade to SDE 9.8 which now includes Web Services that will return the ticket number – I will be doing an article on this very soon.

    The other alternative, which is a bit of a fudge, is that you get your application to generate a unique key on submitting the record e.g: LoggedOnUserName_TimeStamp and store that in a field in the SDE db. Then you can query the db for tickets that match that key and you’ll definitely get the correct result.

    Hope this makes sense.

    Regards,

    Alan

  7. Alan,
    This is really great, much appreciated. So I am trying to do the select client ID query (among others) in SDE 9.6 and I notice that my db does not have the [_SMDBA_].[Incident] table, it looks like it has been deprecated in this version. Do you happen to have any idea where incidents are stored in this version as I am definitely able to create and update incidents via your excellent post instructions.

    thanks,
    Matt

  8. Hi Matt,

    Incidents are stored in the _SMDBA_._TELMASTE_. Every user configurable table in SDE has a view that includes any of the foreign key lookup data that is configured in DBAdmin. The _SMDBA_.Incident is the view to the _SMDBA_._TELMASTE_ table.

    Hope this makes sense.

    Regards,

    Alan

  9. Hi Alan,
    I am trying your webservice and it is slick in a way. When I try to invoke any of the 3 methods I get this error.

    System.Web.Services.Protocols.SoapException: Incorrect syntax near ‘&’.
    at Service.Incidents_SelectByClientID(String sClientID) in c:InetpubwwwrootService Desk API Alan BarberAPIMagicApp_CodeService.cs:line 52

    Any ideas?

  10. Hi Kais,

    I have just downloaded the application again and followed the instructions and all appeared to work perfectly. Can you confirm that you have deleted ALL the comments INCLUDING the set at the bottom?

    Let me know and we’ll go from there.

    Regards,

    Alan

  11. In total gratuity, Alan:

    You have given to the SDE development community is liken to Moses handing down the Ten Commandments. I had spent dozens and dozens of hours trying to come up with a programic approach to the XMLPostHandler / XML Initiator and was still at a loss until I read this post of yours. Thank you so much for the inspiration.

    Now lets forget that SDE 10.0 just released and only consider 9.8. Would you if you know what you know now, have not gone with the xml initiator approach and perhaps gone with the web service integration feature?

    If so, have you done with it as you have done here and posted a programmer tutorial or explanation of some kind?

  12. Hey Alan,
    Didn’t look at your posting until now. I will try it again and will let you know. Haven’t touched it since.

    Thanks

  13. Alan,
    I have a new module and it is either inactive = 1 or inactive = 0 how would you get the webmethod to work for it.
    This is what I have for it.

    [WebMethod(Description = “Function to return the count of open/Closed/both Access Requests for a given Client ID.”)]
    public string AccessRequests_SelectCountBySupID(string sSupID, int sInactive)
    {
    try
    {
    string _Value = “”;
    string _SQL = “SELECT Count(*) FROM [_SMDBA_].[Access Request List] WHERE [Non Super Requestor] = ‘” + sSupID + “‘ AND [InActive:] = 0″;

    {
    _SQL = _SQL + ” AND [InActive:] = ‘” + sInactive + “‘”;
    }
    _Value = _da.Return_Single_Value(_SQL);
    return _Value;
    }
    catch (Exception ex)
    {
    throw _u.Error_Handler(“Return_Single_Value”, ex);
    }
    }

    it returns 0 all the time.

    Thanks

  14. Hi Kais,

    You appear to have [InActive] hardcoded into the main SQL and then added later as well. Remove the first reference to InActive and then remove the apostrophes around sInactive as this is a int and doesn’t need them.

    Add a break point (in Visual Studio) on the Catch (Exception ex) and just make sure that no error is being logged. Alternatively, try running SQL Profiler at the DB and find out what, if any, SQL is being sent to the DB. You can then try running that SQL at the DB manually and see what gets returned.

    Regards,

    Alan

  15. Hi SalaWare,

    Many thanks for the great compliments. In answer to your question about XML Initiator vs WebServices I would definitely plum for WebServices as, as per the comments above, there are a number of issues associated with the XML Initiator. The biggest of these are that it is very difficult to know if your post was successful or not and you can’t retrieve any data from SDE using them. WebServices addresses both these issues.

    I have every intention of writing an article on WebServices in the very near future but, I said that back in Feb 2009 and still haven’t. Life has been a little hectic of late. That said WebServices is a lot more straightforward to use than the XML Initiator so hopefully people are making more progress with this without me 🙂

    Regards,

    Alan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s