"sysapproval_approver" and "sys_user_has_role" could not find query records.

[Madhura Ambede]

We have a requirement that, remove the role "approver_user " for approvers from sysapproval_approver table.

In a sysapproval_approver table, approval state should be approved/rejected/cancelled/No longer required for that approver. we have tried below script and it is well working on personal developer instance but not working other instance. Instance having large set of data.

var approvalApproverGr = new GlideRecord('sysapproval_approver');

approvalApproverGr.addEncodedQuery('stateINapproved,rejected,cancelled,not_required');

approvalApproverGr.query();

while (approvalApproverGr.next()) {

    // Get the user name from sysapproval_approver table

    var userId = approvalApproverGr.getValue('approver');

       //gs.print(" Printing user having APPROVER ROLE " + userId);

    // Query sys_user_has_role table to find the user record

    var userGr = new GlideRecord('sys_user_has_role');

    userGr.addQuery('user.sys_id', userId);

    //add role if need to delete specific roles

    userGr.addQuery('role.name', 'approver_user');

    userGr.query();

    while (userGr.next()) {

             // Remove approver_user role from the user

        userGr.deleteRecord();

    }

}

[Maddysunil]

@Madhura Ambede 

Since you mentioned that the instance has a large dataset, it's crucial to optimize the script's performance. One optimization you can make is to avoid nested queries within loops. Instead, consider using a more efficient GlideRecord query to fetch all relevant records at once.

Please try below code once:

var approvalApproverGr = new GlideRecord('sysapproval_approver');
approvalApproverGr.addQuery('state', 'IN', 'approved,rejected,cancelled,not_required');
approvalApproverGr.query();

while (approvalApproverGr.next()) {
    var userId = approvalApproverGr.approver.toString();
    
    // Query sys_user_has_role table to find the user record
    var userGr = new GlideRecord('sys_user_has_role');
    userGr.addQuery('user', userId);
    userGr.addQuery('role.name', 'approver_user');
    userGr.query();
    
    while (userGr.next()) {
        userGr.deleteRecord(); // Remove approver_user role from the user
    }
    
    // Free up memory
    userGr.close();
}

// Free up memory
approvalApproverGr.close();

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks

[Madhura Ambede]

@Maddysunil ,

Thank you for providing the solution. With the help of above query we are getting record count but unable to delete the role from sys_user_has_role table for approvers from sysapproval_approver table. Kindly assist

[Maddysunil]

@Madhura Ambede 

If you're unable to delete the role from the sys_user_has_role table for approvers from the sysapproval_approver table, there might be reasons  that the user running the script has the necessary permissions to delete records from the sys_user_has_role table.

Also Ensure that there are no database constraints or business rules preventing the deletion of records from the sys_user_has_role table.

Meanwhile I have applied some log to check where code is getting failed:

var approvalApproverGr = new GlideRecord('sysapproval_approver');
approvalApproverGr.addQuery('state', 'IN', 'approved,rejected,cancelled,not_required');
approvalApproverGr.query();

while (approvalApproverGr.next()) {
    var userId = approvalApproverGr.approver.toString();
    
    // Query sys_user_has_role table to find the user record
    var userGr = new GlideRecord('sys_user_has_role');
    userGr.addQuery('user', userId);
    userGr.addQuery('role.name', 'approver_user');
    userGr.query();
    
    while (userGr.next()) {
        // Attempt to delete the record
        if (userGr.deleteRecord()) {
            gs.info('Role "approver_user" deleted for user: ' + userId);
        } else {
            gs.info('Failed to delete role "approver_user" for user: ' + userId);
        }
    }
    
    // Free up memory
    userGr.close();
}

// Free up memory
approvalApproverGr.close();

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks

[Madhura Ambede]

@Maddysunil Thanks for sharing the information and this is indeed helpful.

Now approver_user is removing for user but this is not taking record only for user who is having approver state approved, rejected, cancelled, not_required.

Kindly assist