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

How To Default The Start And End Date of Any Month in SQL Reporting Services

It seems, from experience, that most of the Microsoft SQL Reporting Services reports I write these days, have a requirement for a date range set via parameters. Whilst you can just let your users select the dates they want the report to run, you can also provide default values that are then changeable or not. So I thought I would explain in this very short post how to default a StartDate parameter to be the 1st of the current month and an EndDate parameter to be the last day of the current month. In Visual Studio select Report > Report Parameters.

StartDate

Click Add and enter the following:

Name: StartDate
Data type: DateTime
Prompt: Start Date

In the Default values section select the Non-queried radio-button and enter the expression below in the textbox next to it:

=CDate(“01/” & Month(Now) & “/” & Year(Now))

EndDate

Click Add and enter the following:

Name: EndDate
Data type: DateTime
Prompt: End Date

In the Default values section select the Non-queried radio-button and enter the expression below in the textbox next to it:

=DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, Parameters!StartDate.Value))

Click OK.

All we are doing here is taking the previously calculated StartDate parameter (now, for example, 01/10/2008) and adding a month to it, before subtracting one day. This allows us to get the last day of the month irrespective of how long the month is or whether it is a leap year or not.

Final Word Of Caution Re Defaults

Default parameters make life a lot easier for end users but they must be used very carefully in SQL Reporting Services because, a report will automatically execute if all the required parameters are defaulted. As such if the users generally are not accepting your defaults then they should be remove as it is placing an extra query against the database for no reason. Alternatively, add another parameter that is not defaulted so that, whilst the dates pre-populate, the report wont run until that parameter is set.

Hope this was of help. As always, feedback (positive or negative) is always welcome.

How To Make Sure Two Divs Are The Same Height

I recently needed to make sure that two DIV tags always stay the same height even though both have variable content, driven from a database. I thought I would share the solution I used although would welcome any alternative ways of doing the same thing.

HTML

The basic HTML I started with is shown below:

<html>
<head>
<title>Maintaining DIV Heights</title>
<style type="text/css">
#divLeft
{
float:left;
width:49%;
border:1px solid #000;
}
#divRight
{
float:right;
width:49%;
border:1px solid #000;
}
</style>
</head>
<body>
<div id="divLeft">
<p>
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
</p>
</div>
<div id="divRight">
<p>
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
</p>
</div>
</body>
</html>

So it’s a rather basic file and the idea is that no matter how much text you type into either column, the heights will always remain the same.

Solution

The solution I used involved a little JavaScript that essentially:

  1. Finds the height of the two columns and stores those values in two variables. I have had to check for IE as IE and Firefox/Safari interpret offsetHeight and clientHeight differently.
  2. Decides which is taller – the left or the right.
  3. Makes the shorter column the same height as the taller column.

So I added the function below into the <head></head> area…

<script type="text/javascript" language="javascript">
function setDivHeights() {
if (document.all) {
var iLeftHeight = document.getElementById("divLeft").offsetHeight;
var iRightHeight = document.getElementById("divRight").offsetHeight;
}
else {
var iLeftHeight = document.getElementById("divLeft").clientHeight;
var iRightHeight = document.getElementById("divRight").clientHeight;
}
if (iLeftHeight > iRightHeight) {
document.getElementById("divRight").style.height = iLeftHeight + "px";
}
else {
document.getElementById("divLeft").style.height = iRightHeight + "px";
}
}
</script>

…and then added this piece of JavaScript to call the function in the <body> tag…

onLoad="setDivHeights()"

…such that the end result looked like:

<html>
<head>
<title>Maintaining DIV Heights</title>
<style type="text/css">
#divLeft
{
float:left;
width:49%;
border:1px solid #000;
}
#divRight
{
float:right;
width:49%;
border:1px solid #000;
}
</style>
<script type="text/javascript" language="javascript">
function setDivHeights() {
if (document.all) {
var iLeftHeight = document.getElementById("divLeft").offsetHeight;
var iRightHeight = document.getElementById("divRight").offsetHeight;
}
else {
var iLeftHeight = document.getElementById("divLeft").clientHeight;
var iRightHeight = document.getElementById("divRight").clientHeight;
}
if (iLeftHeight > iRightHeight) {
document.getElementById("divRight").style.height = iLeftHeight + "px";
}
else {
document.getElementById("divLeft").style.height = iRightHeight + "px";
}
}
</script>
</head>
<body onLoad="setDivHeights()">
<div id="divLeft">
<p>
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
</p>
</div>
<div id="divRight">
<p>
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
This is some text - this is more text - this is even more text...
</p>
</div>
</body>
</html>

An example of the solution can be found here. If you have padding as part of the divs then you need to do some jiggerypokery to get the heights to balance again by subtracting the top and bottom padding from the Firefox/Safari side.

For example:

If the top and bottom padding were 10px you would need to subtract 20.

var iLeftHeight = document.getElementById(“divLeft”).clientHeight – 20;
var iRightHeight = document.getElementById(“divRight”).clientHeight – 20;

If anyone has any better solutions than this then I would be very open to it. Anyway, hope this helps. As always, any feedback (positive or negative) is always appreciated.

Default Groups when Logging Into Service Desk Express 9.6

Network Associates/BMC have never supported the concept of users having a default group when they log into Magic/Service Desk Express. Judging by the comments in the forums lately I would suggest that this could be a feature for a future release. There were some rather elegant solutions posted in the forums that I think would do rather well, but in the meantime I set about finding a “workaround” to the issue. What I propose to discuss here is an alternative solution that you can implement yourself that will “remember” the user’s last logged on group between browser sessions by storing a cookie on the user’s machine.

PLEASE TAKE A BACKUP COPY OF THE TWO FILES BEFORE MODIFYING THEM AS THIS IS AN UNSUPPORTED HACK!

Also please be careful that when cutting and pasting from this blog to notepad that “” are not accidentally replaced with a different type of quotes.

Storing the Cookie

So the first step is modifying a file that will allow us to store which group the user has logged on as. There are a bunch of files you could do this but I choose options_nailogo.aspx using a little JavaScript. Open options_nailogo.aspx (C:Program FilesBMCService Desk ExpressApplication Server by default) in Notepad or, more preferably, Notepad ++ (an absolutely brilliant editor that I now use for pretty much everything). Near the bottom of the file you will find code that looks like:

<script Language="JavaScript">
//alert(""+strBaseURL);
window.status =  sUserName + "; " + sGroupName;
window.defaultStatus = sUserName + "; " + sGroupName;
document.all("marqWBNotice").trueSpeed=true
</script>
<script language="JavaScript">clmGetText(document)</script>

We are going to insert a little function between these two functions that stores the cookie on the user’s machine such that code looks like:

<script Language="JavaScript">
//alert(""+strBaseURL);
window.status =  sUserName + "; " + sGroupName;
window.defaultStatus = sUserName + "; " + sGroupName;
document.all("marqWBNotice").trueSpeed=true
</script>
<script Language="JavaScript">
var expiry = new Date();
expiry.setTime(exp.getTime() + (1000 * 60 * 60 * 24 * 30));
document.cookie = "SelectedGroup=" + escape(sGroupName) + "; expires=" + expiry.toGMTString() + "; path=/";
</script>
<script language="JavaScript">clmGetText(document)</script>

Save the file.

Retrieving the Cookie

When a user tabs out of the UserName textbox of the Login screen of Service Desk Express a C# function is called that dynamically builds the HTML of the dropdown list based on the list of groups that the user is a member of. So essentially all we are going to do is modify this function to grab the cookie we placed in the function above (if it exists) and use it to set the selected option of the Group downdown list. This function lives in Login.cs. Open Login.cs (C:Program FilesBMCService Desk ExpressApplication Serverincludes by default) in any text editor and replace the function GetUserGroups with the function below:

string GetUserGroups(string sUsrName)
{
string strGRPName, strGRPSeq, strOutPut;
strOutPut = "";
string strDefaultGRPName = "";
HttpCookie cookie = Request.Cookies["SelectedGroup"];
if (null != cookie)
{
strDefaultGRPName = cookie.Value.ToString();
strDefaultGRPName = strDefaultGRPName.Replace("%20", " ");
}
if (MetaData != null)
{
System.Diagnostics.Trace.WriteLine("[Login] Metadata available.");
NAMMETADATALib.IMUser objUser = (MetaData.Users as NAMMETADATALib.IMUsers).GetUserByName(sUsrName) as NAMMETADATALib.IMUser;
if (objUser != null)
{
for (int i = 0; i < objUser.GroupCount; i++)
{
NAMMETADATALib.IMGroup objGroup = objUser.GetGroupByIndex(i) as NAMMETADATALib.IMGroup;
if (objGroup.IsActive == true)
{
strGRPName = objGroup.name;
strGRPSeq = objGroup.Sequence.ToString();
if (strDefaultGRPName != "")
{
if (strGRPName == strDefaultGRPName)
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + " SELECTED="True">" + strGRPName + "</OPTION>";
}
else
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + ">" + strGRPName + "</OPTION>";
}
}
else
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + ">" + strGRPName + "</OPTION>";
}
}
}
}
else
{
strOutPut = "NOTVALIDUSER";
}
}
else
{
System.Collections.SortedList oUsers=null;
try
{
oUsers = (System.Collections.SortedList)FeatureManager.GetUserGroups(sUsrName);
}
catch(Exception e)
{
string strResponse="<DATA>";
strResponse=strResponse+ "<ERROR>"+ e.Message +"</ERROR>";
strResponse=strResponse+"</DATA>";
strOutPut=strResponse;
return strOutPut;
}
if (oUsers != null)
{
if (oUsers.Count == 0)
{
strOutPut = "NOTVALIDUSER";
}
for (int i = 0; i < oUsers.Count; i++)
{
strGRPSeq = oUsers.GetKey(i).ToString();
strGRPName = oUsers.GetByIndex(i).ToString();
if (strDefaultGRPName != "")
{
if (strGRPName == strDefaultGRPName)
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + " SELECTED="True">" + strGRPName + "</OPTION>";
}
else
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + ">" + strGRPName + "</OPTION>";
}
}
else
{
strOutPut = strOutPut + "<OPTION ID=" + strGRPSeq + ">" + strGRPName + "</OPTION>";
}
}
}
}
return strOutPut;
}

Save the file and that’s it. You may need to clear your browser’s cache and it is probably worth an IISRESET as well.

As always, hope this helps, and any feedback (positive or negative) is always appreciated.