Need to maintain Submitter info in Service Desk
When users are termed and accounts removed from AD and service desk we need to be able to maintain the ticket submitter info. Right now if user is removed the ticket goes to an unassigned submitter. We need to maintain info for reporting and auditing purpose.
If you archive your tickets then the user data in the ticket is maintained regardless of the status of the user account in KACE.
We tested this for a customer a couple of weeks back, if you delete the user account and the ticket is in a live queue then the ID is lost.
If you archive all tickets logged by the user and then delete the user account the user data stays visible
so i got it working with the following
select HD_TICKET.ID as ID,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
JOIN USER U ON (U.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.SUBMITTER_ID <>0 and HD_TICKET.HD_QUEUE_ID = 'X'
UPDATE HD_TICKET T
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE3 = U.FULL_NAME
WHERE T.ID IN (<TICKET_IDS>)
I am going to make an assumption that you are syncing user and authenticating with Active Directory. Even if you are, you do not have to remove the users from KACE. You can disable their account, but you will have to deal with the clutter of old users listed, but that seems to be your desire.
If you want to clean up your KACE user list when you clean up AD so that it is neat and tidy, you can create a ticket rule that runs on Ticket Create so that it copies the submitter info into one of the Custom fields of the ticket and set it so only the admins see it.