The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

How do I move all historical calls from one user to another before deleting the user?

SOLVED
Go to solution
Highlighted
Anthony Livings
Collector

How do I move all historical calls from one user to another before deleting the user?

Hi,

For various reasons we have a number of duplicate 'Person' records in Service Desk.

I have identified these duplicates and which ones should be deleted.

If I select one of the duplicate records from the Person view in SD and try to delete it it shows me a list of up to 100 calls associated with that record.

It gives me options to delete these calls or modify them one at a time.

What I want to do is 'move' (associate) them to the other People record. I don't want to have to modify each call individually or I'll be here till Christmas!

What is the best way to do this?

Cheers,

Anthony.
21 REPLIES
Mark O'Loughlin
Honored Contributor

Re: How do I move all historical calls from one user to another before deleting the user?

Hi Anthony,

use the update all facility - select the service calls in a view - right click and update all. You can associate them to someone else for "Assigned To Person" "Caller" etc but I don't think that you can change the "Entered By Person"

Update all will fail on records that do not have mandatory fields populated - you may have switched on a new mandatory field since the call was saved last.

At first block the duplicate person record in the person form, use the update all and then delete it if necessary. Some you may not be able to delete so update all should be ok.
Anthony Livings
Collector

Re: How do I move all historical calls from one user to another before deleting the user?

Cheers for that - nearly does what I need (in a convoluted way!).

Only thing is - when I change the Caller field to the new Person record it resets the 'Service' field to blank on each record - this is a mandatory field so all record updates fail!

I could reset the 'Service' field on all the records to the same thing but then we would lose historical data analysis.

I now need to find a way of stopping the Service field from reseting to blank when the Caller field is changed.

Any ideas??

:o)
Mark O'Loughlin
Honored Contributor

Re: How do I move all historical calls from one user to another before deleting the user?

Interesting - let me have a look

Don't forget the points along the way- All are appreciated. Thanks.
Mark O'Loughlin
Honored Contributor

Re: How do I move all historical calls from one user to another before deleting the user?

Try this - not sure if it will achieve everything but here goes ...

Set Service an NOT mandatory (maybe do this out of hours and on a test system first)
You loose the audit trail if who set the service and when the service was set

Set up a new text field.
Create a new check box field
Create a Db rule that
-copies the value from the service field to the the new text field when the new checkbox field is "checked"

Do an update all for all records that sets the check box to "checked" and the rule should copy over the value of service to the new text field.

Do the update all as described before to ammend the person record differences

Reset the check box for all the records again and set up another DB rule

Create a Db rule that
-copies the value from the new text field to the the service field when the new checkbox field is "checked"

Do an update all for all records that sets the check box to "checked" and the rule should copy over the value of the new field to service with the pre-existing value.

*** Lots of work here. Wondering if it is easier to export all the records and import the service value after updating the person records.
Dan Ioan
Frequent Visitor

Re: How do I move all historical calls from one user to another before deleting the user?

Hi Anthony,
I suppose that the number of duplicated persons is not high. This means that couldn't affect too much statistics for associated services. Same time, if related service calls to duplicated persons reference different services, there is no way to preserve these using "update all" facility.
We have been facing with the same thing regarding needing to remove duplicated persons. So, you can do the following in order to manage this in the future:
1. Create a custom field named "Former Caller Name) and use it after Caller have been deleted from related service call.
2. Don't relate Caller field to another person! Never! If there are some DB rules for caller notification, after you "update all" service calls, a lot of new notifications will be spread into your organization! Same thing for "Assigned workgroup" and "Assigned person" fields. Clear these fields to avoid assignment notifications if exist via DB rules!
3. Archive periodically service calls. This could helps, because eliminates a lot of relations between persons and old service calls.
Best regards,
Dan
Anthony Livings
Collector

Re: How do I move all historical calls from one user to another before deleting the user?

Cheers guys - Yip lots of work there!

Specially as we are looking at doing this on about 500 of the 6000+ Person records we have!

Still looking for a neater / quicker method.
Perhaps some sort of SQL script to do this at the DB level?

Ideally a feature request for HP!
When you delete a 'Person' give an option to Delete all calls, Modify all calls, or **Associate all calls to another Person (without causing a storm of notifications!).

I'll leave this open for a while to see if anyone comes up with another idea...

Anto.
Brian Alexander
Super Collector

Re: How do I move all historical calls from one user to another before deleting the user?

Here is a possbile way using the web-API, however, this would still fire database rules..

// fill in the correct server, username, & password.
ApiSDSession sd = ApiSDSesion.openSession(server,user,pass);
// find the person records.
IPersonHome home = sd.getPersonHome();
// the OID of the person records must be supplied.
IPerson oldPerson = home.openPerson(oidOfOldPerson);
IPerson newPerson = home.openPerson(oidOfNewPerson);
IServicecallHome scHome = sd.getServicecallHome();
boolean loop = true;
while (loop) {
// find records with oldPerson as the caller.
IServicecallWhere where = scHome.createServicecallWhere();
where.addCriteriumOnCaller(oldPerson);
IServicecall[] sc = home.findServicecall(where);
if (sc != null && sc.length > 0) {
// process each record.
for (int x = 0; x < sc.length; x++) {
long id = 0;
try {
// save the id for status messages.
id = sc[x].getID().longValue();
// retrieve the service.
IService service = sc[x].getService();
// set the caller to the new person.
sc[x].setCaller(newPerson);
// make sure the service stays.
sc[x].setService(service);
// save the changes.
sc[x].save();
System.out.println("sc#"+id+" success.");
} catch (RuntimeException e) {
System.err.println("sc#"+id+" failed.");
}
}
} else {
// stop searching for records.
loop = false;
}
}
Robert S. Falko
Honored Contributor

Re: How do I move all historical calls from one user to another before deleting the user?

Anthony,

I think the reason the service is reset when you try to change the user is that each user has entitlements to services, based on either themselves or on the organization to which they belong. They (as individuals) or their organizations have SLAs which link them to the services. No SLA no service.

Does your new person have the same entitlements as the old?

-Josh
Anthony Livings
Collector

Re: How do I move all historical calls from one user to another before deleting the user?

Cheers for the help guys,

I've been off sick for the last week so apologies for not replying.

Yes the new user has the same entitlement as the old - they are duplicate entries in the Person database - we just need to remove the duplicate name and reassign all the associated historic calls to the remaining 'live' Person record.

Sounds simple doesn't it!

A.

Re: How do I move all historical calls from one user to another before deleting the user?

Anthony,
- select this calls in a view or adv. search.
- update fields Caller and Organization using 'Update All' feature
- try first on 2-3 records and check them after update to avoid any mistakes.

Hope this helps,
Dmitriy
Anthony Livings
Collector

Re: How do I move all historical calls from one user to another before deleting the user?

Dmitriy,

If you read down through the thread you will see that this doesn't work.

Changing the 'Caller' field automatically clears the 'Service' field.

'Service' field is a mandatory field so the update all fails.

The 'Service' field will be one of more than a dozen different values. We can't set the 'Service' field to a single value during the 'update all' as we must retain it's original vaule for historical data analysis.

Anto.
Elias Abboud
Honored Contributor

Re: How do I move all historical calls from one user to another before deleting the user?

Hi Mr. Anthony,

Just as a thought, did you pursue the possibility of using data exchange for this?

What I am thinking of is:

1. Export the records (Persons/Calls) to be modified.
2. Do your modifications on the resulting XML file. You would need to write code for this. Your code would parse the XML, do the neccessary modifications like reassigning the calls to the correct persons and remove links to the duplicate person records and copying the service field....
3. Reimport the resulting XML (Taking care to update existing records and not create new ones !!!)
4. Delete the duplicate persons

This approach should be tested thouroughly on a test system before you apply it to a production system.

Hope This Helps.

PS: Don't forget the points :)
If you can't solve it, post it :)
Anthony Livings
Collector

Re: How do I move all historical calls from one user to another before deleting the user?

Thanks Elias,

We were hoping to avoid any coding as I'm alergic to it and we don't want to have to buy in additional services to complete the task... $$$!

I assume this can be done somehow at the SQL database level without th eneed to export / import XML data...

Something like:
1. Get the ID's of the two Person records we want to 'merge'.
2. Search for all Service Calls and CI's with Person ID = 'A' and replace with Person ID = 'B'.
3. Delete Person 'A' via the GUI.

Again sounds simple but I'm no script monkey and only have a basic grasp of SQL. I had a look at the SQL tables and views and throughly confused myself!

I think we need a 'feature request' from HP!

Anto.
Elias Abboud
Honored Contributor

Re: How do I move all historical calls from one user to another before deleting the user?

Hi again,

Well ok then if you dont like the coding part then you might find some tools on the net that you can use to import your xml file into a temporary database then make your modifications by sql queries. After that export your XML back from the temp DB and import it in service desk :) But even with this I still think you will need some scripting along the way :(

Try it out (on a test system), you might find your way out without coding :)

As for directly modifying SD database, well I tried this for much simpler tasks but was never able to actually modify data like I needed it. As long as you are just reading data from the DB for reporting for example it does not complain.

But if your goal is to modify (or insert) data directly in DB, you will be faced with lots of triggers and checks built into the DB. I went crazy trying to follow the logic in these but never made it to shore :)

Hope this helps
If you can't solve it, post it :)
Elias Abboud
Honored Contributor

Re: How do I move all historical calls from one user to another before deleting the user?

Which relation between person and call? is it caller or assignement relation that you want to move?
If you can't solve it, post it :)
Elias Abboud
Honored Contributor

Re: How do I move all historical calls from one user to another before deleting the user?

Ok since you seem to be more of a DB dude and you want to go the DB way here is some pointers for you (Supposing you are on SD5 SP1 on WIN2003SP1 and MSSQL 2000 SP4):

For the service calls
assignement link is in sd_servicecalls.ser_ass_per_to_oid

Caller link is in sd_servicecalls.ser_caller_per

Other fields might also be needed like the assigned workgroup and the caller organization as well as the link to the service id.

!!!WARNING!!!
-------------
3 triggers and several indexes are setup on that table. So If you can figure out the logic then you should be on the right track.

Hope This helps
If you can't solve it, post it :)
Anthony Livings
Collector

Re: How do I move all historical calls from one user to another before deleting the user?

Example:

There are two Person records with the same display name and Short Code:

BloggsJ - Joe Bloggs
BloggsJ - Joe Bloggs

(A and B for arguments sake.)

I have found these entries in the ITSM_Persons table and they have different PER_OID numbers - which I assume identifies them in the database.

I need to find all Service Calls were A is the caller and replace these with B as the caller.

In the ITSM_Servicecalls table SER_CALLER_PER would seem to refer to the PER_OID field to get the Person data.

It looks like it should be a simple matter of finding all occurances of SER_CALLER_PER = A and replacing them with SER_CALLER_PER = B. ???

Now where did I put my "SQL for Dummies" book?? :o)

Anto.
Elias Abboud
Honored Contributor

Re: How do I move all historical calls from one user to another before deleting the user?

Ok I did another test (SD5):

UPDATE sduser.sd_servicecalls
SET ser_caller_per = 163725750075369771
WHERE (ser_caller_per = 163725750075369741)

And checked result in SD GUI and it seems to work.

But then I dont guarantee there are no other links to that table that might be affected.

Hope this helps

PS: dont forget the points
If you can't solve it, post it :)
Elias Abboud
Honored Contributor
Solution

Re: How do I move all historical calls from one user to another before deleting the user?

In your case try (on a test system):

UPDATE ITSM_Servicecalls
SET ser_caller_per = CorrectPersonOID
WHERE (ser_caller_per = DuplicatePersonOID)

Hope this helps

If you can't solve it, post it :)
Anthony Livings
Collector

Re: How do I move all historical calls from one user to another before deleting the user?

Spot on - this seems to be as close as we'll get... :o)

On an aside - we've just had a brief meeting and been told we are moving to a new version of Service Desk (currently on 4.5).

As part of that work we will be drawing a line under the current version, archiving off all data, and doing a fresh install repopulating all users from AD and starting from scratch!

Wish I'd been told that a couple of weeks ago when I was asked to look at this problem as it will soon be a redundant system and hence this is a redundant exercise!

:(

Thanks to all for their help - I hope this thread helps others in the future.

Anthony.
Anthony Livings
Collector

Re: How do I move all historical calls from one user to another before deleting the user?

The comments from Elias above seem to resolve this problem although further system testing would be needed to ensure there is no knock on effects...

Thanks!
//Add this to "OnDomLoad" event