SYS_User Table Clean up(Deleting/Removing)

[eachand]

I need to clean our sys_user table but before i want to ensure I do not remove accounts that are already tied to task record,

Has anyone done this?

What steps were taken?

What are the pitfalls?

I realize removal and deleting of users goes against best practices however we have a lot of bad data from our identity provider during the past couple of years.

[ghsrikanth]

First place to start is,

Go to sys_dictionary table

And put reference = sys_user

Note down all the tables which are referencing the User table -

And make sure before you delete the user from sys_user table, you either replace this reference or remove this reference from the records of those tables.

In this way, you will prevent orphan records

[eachand]

Hi Srikanth,

Thanks for the prompt response, will provide a bit more context to my nightmare I have close to 200K user records(sys_user) and approx 600K task (Change, problem and incidents) records

I need to figure out which users are not within the task records so I can remove them, doing it using SQL will be easy just not sure how to use GlideRecord to achieve this script and iteration will not work. Looking for a way to subset this data into reference and non referenced.

Thanks in advance

Ed

[mloranc · Accepted solution]

Hi Ed,

We had to do this about a year ago probably for similar reasons that you're facing. The way I approached it was to set up a checkbox on the User records for "Has record" that would get marked whenever a person has something to do with a ticket. I wrote a fix script to do the first pass of all records to search for and tag all associations for existing incidents. I ended up breaking it into a few passes based on dates added to the query because we had so much data that needed to be parsed through. After the initial pass, I created a few business rules on the appropriate tables to check the box whenever a record is inserted or updated on key fields. For instance, on Incident, it would flag the person listed in the caller_id field for "Has record."

The final step was purging. After all your user records have been marked, just remove the ones that aren't. That cleanup process was extensive because record deletes take time. The initial cleanup was broken into pieces with a couple fix scripts.

Long term was a bit more complicated. We have our system feed users daily but it would be incredibly inefficient to remove all, and then add back in. Not to mention very time consuming. In addition to the "Has record," I added a "Currently in feed" checkbox. Before the feed, all users get this field cleared. On the import, it gets checked if that record gets updated with the feed. After the feed completes, a nightly scheduled job runs to purge any users that are false for "Currently in feed" AND "Has record".

In the end, it was essentially this: 2 checkboxes added to the caller record, a business rule per table that needs to be watched, and a scheduled job for cleanup.

Feel free to message me if you need more specifics.

[Phil O_shea]

Hi Michael,

Would you mind sharing the script that you used to flag the checkbox on the sys_user table wherever that account is used as a reference related to a ticket?

We are in the process of doing a cleanup of user accounts and like your approach with regards to the one-time fix script that you used.

Thanks & Regards,

Phil