Help with scripting on a fix script

[Cirrus]

Struggling with a lack of scripting knowledge here.

We are trying to remove a large volume of entries from the sys_report_users_groups table where the user has been archived (but its not immediately obvious which users are archived). I think the logic needs to be this:

For each user id in this table, get the sys_id value

Create a glide ajax call with parameter = sys_id value

Create a script include to look for the sys_id value in the user table

If found, return true

If response is true, keep record in report table, else delete

So far I have the following. Can anyone advise if this is going to loop around over 100,000 records and get the sys_id of the user id field each time (and what would happen if the field is empty, which many will be where is showing the group relationship)

var ga = new GlideRecord('sys_report_users_groups');

        ga.addQuery('sys_id', 'name');

        ga.query;

        while(ga.next()) {

        var archiveduser = new GlideAjax('deleteReportRelationships');

        }

[Brian Lancaster]

Couple things here. You are not querying anything as you have sys_id, name in the add query. By doing that you are saying telling the system to look for a record in the sys_report_users_groups table with the sys_id of name which would not exist. 

Where are you trying to run this script from?

Is this something you are going to run more then once?

[Cirrus]

Thanks Brian, clearly lots to learn when it comes to scripting.

[Juhi Poddar · Accepted solution]

Hello @Cirrus 

• The approach here is to get the sys_id of user_id field from "sys_report_users_groups" table.
• Check for the record with same sys_id in sys_user table

Refer the script:

var grReportUsers = new GlideRecord('sys_report_users_groups');
grReportUsers.query();

while (grReportUsers.next()) {
    var userSysId = grReportUsers.user_id; // Assuming 'user_id' is the field storing the sys_id of the user

    // Check if the user exists and is archived (inactive)
    var userGr = new GlideRecord('sys_user');
    if (userGr.get(userSysId) && userGr.active == false) {
        // User is archived, delete the record
        //grReportUsers.deleteRecord();
        gs.info('Deleted record for user ' + userSysId);
    } else {
        // User is not archived, keep the record
        gs.info('User ' + userSysId + ' is not archived, record retained');
    }
}

Hope this helps!

"If you found my answer helpful, please like and mark it as an "accepted solution". It helps others find the solution more easily and supports the community!"

Thank You
Juhi Poddar

[Cirrus]

Juhi, thankyou. A full solution was more than I was expecting