I have a recurring task where I have to do account management to ensure that licensed accounts for terminated (or transferred) employees are locked.
I am not 'deleting' licensed accounts because I do not want to affect tickets that were resolved/assigned to former-agents.
I just realized that next year when I perform this activity, I will be unable to indicate which accounts have already been reviewed. Does anyone have a best practice or a suggestion for this? How do you manage accounts?
Side question: Can I make changes to the User Information table (for example; create my own date field) without affecting anything?
From a historical tickets perspective, the last person having the ticket assigned to them will remain the "ticket owner" even if you delete them as a Technician from the system. I have removed plenty of accounts and our reports and searches will still show the removed Technicians name. It will remain there unless you try to edit the field which holds their name.
What I do to make sure assigned tickets are transferred: Before I delete the user I run a OneStep which prompts for the User which will be deleted and reassigns all tickets in his queue to the Suggested Teams Queue Manager for redistribution. The trick being you need to convert the technician name into their RecID in order to match them up to their records.
To your side question: Short answer is no, you cannot directly modify any of the system tables from the application. You could possible create a read only SQL view, pull and manipulate that data and save it somewhere else for use in the application but this starts to get into more complicated territory.
Of course if you are a SQL Guru you could make whatever changes you like to the tables from SQL but Support will not be able to help you if something goes bad.
To your side question about the 'user information table' - You can actually customize the UserInfo table however you'd like. You can add fields, change how the existing ones work, promote to supporting or major business object, replace UserInfo with a Customer Group Member instead using a business object attribute..., the largest consequence you may run into would be if you started playing with the fields that hold system functions, you might cause some data-loading issues with those. But even then, it wouldn't catastrophically break your system.
The reason for this is that the userinfo table just holds data about the current user, it isn't used for authentication, etc. That's stored in the TrebuchetAuth table.... which for the most part, we'll just say there aren't many changes to be made to anyways. That table stores info about how a user will sign in (user ID, ad or cherwell-auth, password hash, security group ID, etc).
The userinfo table is demarcated with a special general attribute that decides that it will be the table that's linked to the TrebuchetAuth record and used to hold that user's info. From here, Holds:() attributes on fields are used to load system function data, and the RecID field on this table (or any other if used instead) is used for populating foreign keys / ID's to the userinfo record.
With that said, that special attribute is the only 'special' thing about the user info table - It otherwise behaves like any other table in the system, and could be replaced with any other table, or configured differently like any other table, as needed, with the caveat that you shouldn't greatly disrupt the way those system function fields are used, or you'll break the system functions related to them (note - I'm talking about the tokens for system function data, like CurrentUserDisplayName).
Feel free to add as many new fields to your userinfo table as you'd like.
As a fun example, here's ours (that section on the right is conditionally only visible to admins, even):
In addition to what Rob said - If we really were talking about an unreachable system table, and modifications were needed - you can actually map a new non-read-only, External business object to the system tables and can technically make field value edits from there, just like if you mapped to a table in an external db.
Now, I wouldn't recommend that last bit unless you knew exactly what you were doing and were willing to risk the consequences of breaking whichever table you were playing with in SQL (and potentially breaking your system unless you corrected any newly caused issues), but it's important to note that it is well within the realm of possibility.
Going back to your original question - I personally try to avoid deleting user accounts, for various reporting reasons, but there are very good arguments both to keeping and removing the accounts. I think you'd want to decide what you want to do in your situation.
If you are deleting the accounts, though, I'd recommend setting up a lookup table somewhere for you to log the deletions (name, date, userinfo RecID just in case).
Awesome! Thank you Doug for the clarification and additional info.