Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Technician Quit Leaving 95 Incomplete Tasks

Created: 14 Jan 2013 • Updated: 18 Jan 2013 | 6 comments
This issue has been solved. See solution.

Hello,

We had a technician quit.  They left 95 Incomplete Tasks.  Is there an out-of-box method for reassigning all those tasks at one time or does each individual task have to be opened and reassigned?

Comments 6 CommentsJump to latest comment

jpellet2's picture

I know you can close multiples but I don't believe, outside of a workflow or direct DB manipulation, that there's a way to re-assign multiples all at once. There could be and I haven't come across it yet, but I haven't seen anything.

WK01's picture

Have you ever attempted to make that type of direct database change?  I found the table and field that need to be updated, but my worry is that there are other tables in the system where it may be stored that could potentially break the tasks.

michael.george's picture

Assuming these are all actually assign to a person and not to a group (or queue if using 7.5), you can check this query to make sure you got all the stuff. Obviously, change the DB name and the users email address to match your need.

USE [ServiceDeskDB]
SELECT TA.TaskID
      ,TSK.WFTaskNumberPrefix
  FROM [dbo].[TaskAssignment] TA
    INNER JOIN dbo.Task TSK ON TA.TaskID = TSK.TaskID
    INNER JOIN dbo.[User] USERS ON TA.ReferenceID = USERS.UserID
  WHERE TSK.IsCompleted = 0
    AND TA.ReferenceType = 1
    AND USERS.PrimaryEmail = 'OldUser@email.com'
 
Assuming that pulls back the right stuff, you can go ahead with a change to assign them to a different person. If you want to assign them to a group, it's a little different as you'd want to set the [ReferenceType] to 2 and lookup the GroupID instead of UserID from the [Group] table using [GroupName] instead of email.
 
 UPDATE dbo.TaskAssignment SET
   ReferenceID = (SELECT UserID FROM dbo.[User] WHERE [PrimaryEmail] = 'NewUser@email.com')
 WHERE TaskID IN (SELECT TA.TaskID
  FROM [dbo].[TaskAssignment] TA
    INNER JOIN dbo.Task TSK ON TA.TaskID = TSK.TaskID
    INNER JOIN dbo.[User] USERS ON TA.ReferenceID = USERS.UserID
  WHERE TSK.IsCompleted = 0
    AND TA.ReferenceType = 1
    AND USERS.PrimaryEmail = 'OldUser@email.com')
 
While I haven't ever done this exact query, it does seem to work just fine and I have done similar ones before. Obviously this is more of a rough draft and may require slight tweaking depending on what you are working with.

Case in point here as WK01 needs to adjust permission as well. This one will replace all permissions for the old user with the new user on ALL processes, open or closed. It could easily be limited by joining to the ReportProcess tables on the sessionid and putting more limitations in the where clause.

UPDATE dbo.ReportProcessPermission SET
 ReferenceID = (SELECT UserID FROM dbo.[User] WHERE [PrimaryEmail] = 'NewUser@email.com')
WHERE ReferenceID = (SELECT [UserID] FROM dbo.[User] WHERE PrimaryEmail = 'OldUser@email.com')
 

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

SOLUTION
WK01's picture

Thanks for the help.  I tried it out but now I have a permission problem.  The Process Assignments show the task, however, the Smart Tasks do not function.  I had to set the tasks back to the original user.

It looks like I may have to reset the terminated employee's password, login under his account, and then manually reassign all tasks.

Thanks for providing the query.

michael.george's picture

Sounds like you don't need it, but I'm guessing this is due to the new user not having permission to edit on the tickets. Assuming you can't just add the new user into the same groups as the old user, which would be the case if the permissions had been to the user directly and not via group, i edited my post above to include a basic permissions change query.

If a post solves your issue, please mark it as a solution. It makes these forums better for everyone.

WK01's picture

Thanks for the help.  Everything is working out properly now.