SQL Queries to Help Merge Helpdesk Contacts

In large environments, users sometimes change their email addresses as they migrate from department to department. This can cause a headache in areas where a pucker Identity Management system isn't in place. The result can often be that a single user has a multiple identities in the contact database and a split incident history.

To resolve this, today's article provides a rough-and-ready SQL query which can help first to identify duplicate contacts, and thereafter to coalesce their incident history.

First Task: Identify Potential Duplicate Contacts

In order to establish whether you do have duplicate contacts, we need to run a SQL query on the Helpdesk database, altiris_incidents. In Microsoft SQL Server Management Studio, open up a 'New Query' window, and past in the following code,

 SELECT [name], 
         [contact count] 
 FROM  (SELECT  [name], 
               Count(* ) AS 'Contact Count' 
       FROM   contact 
       WHERE  [status] LIKE 'a' 
       GROUP BY [name]) xxx 
 WHERE [contact count] > 1
 

This code is pretty staightforward; it searches the active contacts in the contacts table grouping all the contacts with the same name together so that they can be counted. If the same contact name should appear more than once in the table, this will dumped to screen.

Jason Smith  2
Kevin Murpy  2
Donna Jenkins 2
Fiona Bruce  2

So, in the above sample output we have found 4 contacts which are possible duplicates. To verify that we indeed have a duplicate (you could simply have employees who share the same name), we need to look at them in more detail.

Second Task: Verify whether extra contact(s) are duplicates

To start off with then, let's take a look at Jason Smith. Open up the Altiris Console, and in the Incidents tab locate the contacts navigation option in the left hand pane. Select 'Find Contact'. In the screen grab below you can see that i've done a search for 'Jason Smith'. Two contacts are presented (as expected from the results of the SQL query),

If we're lucky, the helpdesk contacts database has lots of information which can help acertain whether in fact we do have two Jason Smiths, or just one Jason appearing twice. Lets take a deeper look at each of the entries with a good old double-click on each.

When looking at the above contact information, we can see clearly that the employee IDs are the same leaving no doubt that this is a duplicate -Jason Smith has simply moved from the purchasing to sales. On doing so, his email has changed and the helpdesk staff have created a new contact when opening a call for this previously unknown email.

Here's what can you do when the employeee ID isn't present,

• Use the NT/ID

  In many organisations, as the user moves around internally their logon ID will remain the same. If the NT IDs are available and they are the same then contact is definently a duplicate. Whether the opposite is true however depends on your organisations internal processes.

• Browse the ticket history

  This can often reveal whether we are talking about the same person or not. In the case of Jason for example, his email wasn't configured appropriately on the move, so his first ticket in his sales role is talking about the fact he hasn't been able to send mail since his move from purchasing to sales. Bingo.

• Use Internal Data Sources

  Most departments and organisations have their own databases which can be scavenged either by direct DB access or with a phone call. You can usually find out within a couple of minutes whether the contact is a duplicate.

• Call the User

  The last option is to just call the user directly, and ask them to confirm whether they have indeed moved or not. These calls can often be illuminating -"Oh yes -there is another Jason Smith and i'm forever getting his phone calls/email".

Third Task: Merging Contact Incident History

Having identified that our two 'Jason Smiths' are indeed the same person, the next task is to merge their incident history. From a SQL point of view, to merge their SQL history we need to identify the contact IDs. These can be obtained directly from the id=xxxx closing string in the URL, or you can use the SQL code below which gives some extra info like who created the contact and when,

 SELECT [name],
    [id],
    [nt_id],
    [phone],
    [status],
    [created_on],
    (SELECT [name]
    FROM worker
    WHERE id = contact.created_by_worker_id) AS 'created_by'
 FROM contact
 WHERE ([name] LIKE 'Jason Smith') 

For Jason Smith, the old contact ID for his purchasing identity was 42790 and the new one for him in sales is 42792. To consolidate, we must move his tickets from his old identity to his new one. This can be performed with the following SQL,

 USE altiris_incidents;
 
 DECLARE @GoodContact INT
 DECLARE @BadContact INT

 SET @GoodContact = 42792
 SET @BadContact = 42790

 UPDATE workitem
 SET contact_id = @GoodContact
 WHERE contact_id = @BadContact

This SQL code uses the SQL UPDATE command to change every instance of the old contact ID in the workitem table in the incidents database into the new contact ID. This is a one-way operation really, so be very sure to have backed up your database before beginning.

The screen grab below illustrates how this query has migrated the incidents from the Jason's purchasing contact ID to his sales one. This leaves the Jason's purchasing contact instance free of incidents, and therefore it can now be deleted without fear of losing that incident data.

Summary

What I've tried to show here is a way of consolidating incidents which are split between two contacts. This allows you to harvest incidents from a stale duplicate contact before deleting them. Even though I have focused today on duplicate names, you can of course look for instances of duplicate NT_ID or telephone numbers to assist you in tracking down contact duplicates. I generally think this to be worth while -incidents assigned by helpdesk staff to a duplicate contact can result in emails being sent to user email addresses which are no longer being monitored. Not fabulous for user relations!

I am not sure if this is something Altiris supports, or even if there are better ways of doing this, so comments welcome!

Kind Regards,
Ian./