Using AuxData in Helpdesk to Store a Contact's Manager's Info from an External Source to Facilitate Approval Routing

This example uses a few automation techniques available in Helpdesk to access data from any external source, such as an HR database, for the purpose of automating approvals on service requests. It also uses the Linked Servers feature of SQL Server to facilitate a database level integration.

At a high level the sequence of events is as follows:

1. A service request is made by phone or internet.
2. An Incident Rule runs a query that pulls the contact's manager's name and email address from the external data source and passes it to the AuxData record for the request.
3. An email is triggered that sends an approval request to the manager.
4. The manager either rejects or approves the request.

Setup:

Figure 1: Set up a Linked Server in SQL Server.

Click to view.

This could be a local or networked instance of SQL Server, or even 3rd party databases with networked instances (e.g. Oracle on unix, UDB, etc.). The nice thing is that once you make the connection, you don't have to remember connect strings or anything like that. You just call the database with an alias in your SQL statements. In my case, I created a second local instance. In that instance I created a database called "hr" that has just two tables – employee and manager.

Figure 2: In the instance where your NS database is located, navigate to Security > Linked Servers. Right-click and select 'New Linked Server'.

Click to view.

Use contextual help to find out how to link to your specific database type. You'll find that this is very much like ODBC.

Figure 3: In my case, the "remote" database is actually a local instance, which makes it a reasonably simple configuration.

Click to view.

I only need to supply a linked server name, a network location (the FQN of the instance) and the catalog, or actual database name.

Figure 4: Now the database is fully linked and its tables are visible in the local instance.

Click to view.

Figure 5: As a test, use Query Analyzer to run a query on the linked server. In my case, I used it to present my manager's name and email address.

Click to view.

The syntax for structuring your query is like any ordinary table except that you call the database as an alias. The syntax is [linked server name].[catalog].[schema].[object_name]. In English, that's [linked server name].[database].[user].[table]. Also, because of this it is not necessary to provide a USE statement or select a database from the dropdown.

-----

Figure 6: Now you will need to create a new Incident Rule in Helpdesk to set the AuxData. You will set two properties – each of which is an AEXQUERY that will query the linked database.

Click to view.

One query will populate AuxData with the manager's name and the other his/her email address. This example will not actually use the manager's name, but it is left in for "how to" value as it may come up frequently. Make this rule active whenever 'Status' is equal to 'Open'. This ensures that the data gets stored for every workitem you create, but you can choose to narrow down the criteria if you wish.

An example query for the manager's name is as follows. You will need to "flatten", or remove line breaks from the queries in order to paste them into the value box for the AuxData property:

AEXQUERY[[SELECT m.fullname FROM cleveland.hr.dbo.manager m JOIN cleveland.hr.dbo.employee e ON m.id = e.managerid WHERE e.empname like 'WORKITEM(contact_name)']]

The second query is for the manager's email address.

AEXQUERY[[SELECT m.email FROM cleveland.hr.dbo.manager m JOIN cleveland.hr.dbo.employee e ON m.id = e.managerid WHERE e.empname like 'WORKITEM(contact_name)']]

Figure 7: When setting Auxillary Data, you must provide a member and a name. This corresponds to a virtual table/column in the auxdata field that is stored for each workitem.

Click to view.

The "to value" is where you paste in the query:

Figure 8: The actual AuxData field is just a long text field. The data is stored in properly formed XML which can then be easily called from AUXDATA macros.

Click to view.

You can clearly see from the resultset that the values for the manager's name and email have been added to the XML in auxdata. However, you may not have access to Query Analyzer, or for that matter know how to find the data if you did.

Figure 9: Therefore, as a workaround, you can use a Helpdesk email template to display the AuxData using macros.

Click to view.

If you recall, our member/name set was set to Manager/Name and Manager/Email. This is like forming a virtual table called 'Manager' in AuxData with two columns called 'Name' and 'Email'. Therefore, in order to extract the data back out, we'll call it with the AUXDATA macro, which follows the following form:

WORKITEM_AUXDATA(member, name)

So in our case that's:

WORKITEM_AUXDATA(manager, manager_name)

And

WORKITEM_AUXDATA(manager_email)

Construct a simple (text-based) email template with the following syntax for the message body:

AuxData test for Workitem #WORKITEM(workitem_number)

Manager Name: WORKITEM_AUXDATA(manager, manager_name)
Manager Email: WORKITEM_AUXDATA(manager, manager_email)

Then test it with context by previewing with an incident number.

-----

So far we've configured a linked server, then created a rule to simply extract data from one database and add it to a record in another, and test that the data exists. Now we need to configure the actual approval routing automation. There are myriads of ways to generate routing automation from Helpdesk. For the purpose of this article, we'll choose a simple method of sending a nicely formatted HTML email to the approver, upon which he/she can link to the ticket and either set the status to 'Approved' or 'Rejected'. We'll stop after that because the process should become pretty conventional from there.

Figure 10: The first thing to configure is the Email Template.

Click to view.

For simplicity, I have attached an export file that contains all of the necessary rules and templates mentioned in this article. You can simply import them into your Helpdesk and begin to use them. I've called the template "SR – Send approval to contact's manager". This HTML template extracts the workitem's number, title, and comments to display on the email, and then provides links for the approver to either reject or approve the request.

Figure 11: Next, of course, you need a Notify Rule in order to utilize the Email Template.

Click to view.

Create a Notify Rule called "SR – send approval request to contact's manager" and make sure you select the template you created. In the 'To:' line, under "These addresses:" type the AUXDATA macro - WORKITEM_AUXDATA(manager, manager_email). That will ensure that the email gets routed to the appropriate manager. In my example, I have set the Routing Rule to execute anytime the Category field contains 'Service Request'. Therefore, although the manager's data is being stored for every ticket, it's only used for those that are categorized in a certain way.

Figure 12: The configuration is done and it's time to test.

Click to view.

I've started by creating a service request for new hardware. I've pasted some specs into the comment field and categorized it as Service Request/Add/New Hardware. Once saved, all the automation takes place resulting in an email to the manager. This is the upper page of the workitem.

Figure 13: This is the lower page of the workitem.

Click to view.

Figure 14: The resulting email.

Click to view.

To find out how to do this integration with Asset Control, refer to this article.