Using Auxillary Data in Helpdesk to Store a Contact's Manager's Info from Asset Control to Facilitate Approval Routing

This example uses a few automation techniques available in Helpdesk to access data from Asset Control for the purpose of automating approvals on service requests.

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 Asset Control 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: First, make sure that manager hierarchy is defined in Asset Control. Each user record has an association tab in which the manager can be selected.

Click to view.

If the manager does not exist, one can be manually added, or hierarchy data can be imported using Connector Solution (not covered in this article).

Figure 2: Next, 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 Notification Server 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.

The query needed 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 ISNULL(child.[Name], 'No Association') AS [Manager] FROM vResourceEx parent INNER JOIN ResourceType rt ON parent.[ResourceTypeGuid] = rt.[Guid] LEFT OUTER JOIN ResourceAssociation ra ON parent.[Guid] = ra.[ParentResourceGuid] AND ra.[ResourceAssociationTypeGuid] = '049c633f-8413-42ae-93ea-f4eb7edafc65' LEFT OUTER JOIN vResourceEx child ON ra.[ChildResourceGuid] = child.[Guid] LEFT OUTER JOIN Inv_Global_User_General_Details t1 ON child.[Guid] = t1.[_ResourceGuid] WHERE parent.[ResourceTypeGuid] IN (SELECT ResourceTypeGuid FROM ResourceTypeHierarchy rth INNER JOIN ResourceType rt ON rth.[BaseResourceTypeGuid] = rt.[Guid] WHERE rt.[Name] = 'User') AND parent.[Name] like 'WORKITEM(contact_name)']]

*note: These queries were not manually constructed, but rather, were mostly derived by using the Resource Management Metadata report available in the Notification Server.

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

AEXQUERY[[SELECT ISNULL(t1.[Email], 'N/A') AS [Email] FROM vResourceEx parent INNER JOIN ResourceType rt ON parent.[ResourceTypeGuid] = rt.[Guid] LEFT OUTER JOIN ResourceAssociation ra ON parent.[Guid] = ra.[ParentResourceGuid] AND ra.[ResourceAssociationTypeGuid] = '049c633f-8413-42ae-93ea-f4eb7edafc65' LEFT OUTER JOIN vResourceEx child ON ra.[ChildResourceGuid] = child.[Guid] LEFT OUTER JOIN Inv_Global_User_General_Details t1 ON child.[Guid] = t1.[_ResourceGuid] WHERE parent.[ResourceTypeGuid] IN (SELECT ResourceTypeGuid FROM ResourceTypeHierarchy rth INNER JOIN ResourceType rt ON rth.[BaseResourceTypeGuid] = rt.[Guid] WHERE rt.[Name] = 'User') AND parent.[Name] like 'WORKITEM(contact_name)']]

Figure 3: 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. The “to value” is where you paste in the query.

Click to view.

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. The "to value" is where you paste in the query:

Figure 4: 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 in Figure 4 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 5: 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 created a rule to simply extract data from one database and add it to a record in another, then 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 6

Click to view.

The first thing to configure is the Email Template. 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 7: Next of course, you need a Notify Rule in order to utilize the Email Template.

Click to view.

Create a Notify Rule also called "SR – send approval request to contact's manager" and make sure you select the email 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 8: The configuration is done and it's time to test. I've started by creating a service request for new hardware.

Click to view.

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 9: This is the lower page of the workitem showing the comments.

Click to view.

Figure 10: The resulting email.

Click to view.

To find out how to do this integration with an external data sources, refer to this article.