SQL Queries to Help Merge Helpdesk Contacts

Created: 27 Apr 2009 • Updated: 27 Apr 2009
ianatkin's picture
Login to vote
+11 11 Votes

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),

View Inline Image

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.

View Inline Image

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.

View Inline Image

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./

Filed Under

Tags:

Comments

30
Apr
2009

This is pretty good, one question though

when running the sql to update the incidents database, does it update workitem_detail_view as well. In the past I removed a few contacts and noticed in the history of their incidents the name was removed caused a default name to be used. I was only thinking about the current view and not both.

ianatkin's picture
ianatkin
Trusted Advisor
06
May
2009

Hi Drew, I think that when

Hi Drew,

I think that when you delete a contact, as their contact ID no longer exists the helpdesk revises the incident history changing all contact ID references to zero.

As for your question concerning the views, as I am updating the workitem table which underlies each of of the various workitem views, the views will also be updated. Remember, the views are in essence SQL querys which present data from the underlying tables in an arrangement more suited to specific tasks. This is why view data should never be altered directly -always seek the underlying table structure.

Kind Regards,
Ian./

Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads

dcudney's picture
dcudney
Accredited
Certified
16
Aug
2011

I used this process....

I just used this process to clean up our contact database of duplicates. I also used [email], [nt_id], and [external_id] (which we use for employee numbers) to check further for duplicates. It worked really well for me. Thank you for posting this.

Dewayne Cudney

12
Jul
2013

I am fairly new to SQL, just

I am fairly new to SQL, just started 4 days ago to be honest.  Does the above work for multiple duplicates or only duplicates of 2? Your help is appreciated

 

Thank you,

Kenny

ianatkin's picture
ianatkin
Trusted Advisor
22
Jul
2013

HI Kenny, I've assume here

HI Kenny,

I've assume here we're looking at the duplicate scenario where we have one good contact, and another 'bad' one (the duplicate).

If you want to handle the situation where you have more duplicates, you can simply run the query multiple times with different 'bad contact' values.

That make sense?

 

Ian Atkin, IT Services, Oxford University, UK

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads