"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 · Accepted solution]

@Madhura Ambede 

To ensure that the script deletes the "approver_user" role only for users who have the state approved, rejected, cancelled, or not_required in the sysapproval_approver table, you need to filter users based on their approvals.

// Create a set to store user IDs associated with approved, rejected, cancelled, or not_required states
var userIds = new GlideAggregate('sysapproval_approver');
userIds.addQuery('state', 'IN', 'approved,rejected,cancelled,not_required');
userIds.addAggregate('GROUPBY', 'approver');
userIds.query();

// Array to store user IDs needing role deletion
var usersToDeleteRole = [];

while (userIds.next()) {
    // Store user IDs in the set
    usersToDeleteRole.push(userIds.approver.toString());
}

// Query sys_user_has_role table to find users with role "approver_user"
var userGr = new GlideRecord('sys_user_has_role');
userGr.addQuery('user', 'IN', usersToDeleteRole);
userGr.addQuery('role.name', 'approver_user');
userGr.query();

// Iterate over users with role "approver_user" and delete the role
while (userGr.next()) {
    // Attempt to delete the record
    if (userGr.deleteRecord()) {
        gs.info('Role "approver_user" deleted for user: ' + userGr.user.name);
    } else {
        gs.info('Failed to delete role "approver_user" for user: ' + userGr.user.name);
    }
}

// Free up memory
userGr.close();

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

Thanks

[swathisarang98]

Hi @Madhura Ambede ,

If this is happening due to large data then you can add userGr.deleteMultiple();

Please mark this comment as Correct Answer/Helpful if it helped you.

Regards,

Swathi Sarang

[Amit Gujarathi]

HI @Madhura Ambede ,
I trust you are doing great.
Please find the updated script as given below :

// Create a GlideAggregate to efficiently query sysapproval_approver table
var approvalApproverGr = new GlideAggregate('sysapproval_approver');
approvalApproverGr.addQuery('state', 'IN', 'approved,rejected,cancelled,not_required');
approvalApproverGr.addAggregate('GROUPBY', 'approver'); // Group by approver field
approvalApproverGr.query();

while (approvalApproverGr.next()) {
    var userId = approvalApproverGr.getValue('approver');
    
    // Create a GlideRecord to efficiently query sys_user_has_role table
    var userRoleGr = new GlideRecord('sys_user_has_role');
    userRoleGr.addQuery('user', userId);
    userRoleGr.addQuery('role.name', 'approver_user');
    userRoleGr.query();

    // Iterate through the user roles and remove 'approver_user' role
    while (userRoleGr.next()) {
        userRoleGr.deleteRecord();
    }
}