How to check whether user having approver role but not approved or rejected from last 3 months?

[Enabler]

How to check whether the user has an approver role but has not been approved or rejected in the last 3 months?

This one's got tricky, just looking for suggestions for better solutions.

[Sebastian R_]

You can use Related List Queries (https://www.servicenowelite.com/blog/2022/5/2/related-list-queries-using-gliderecord) either in a report or as encodedQuery for GlideRecord.

roles=approver_user^RLQUERYsysapproval_approver.approver,=0,m2m^stateINapproved,rejected^sys_created_onONLast 3 months@javascript:gs.beginningOfLast3Months()@javascript:gs.endOfLast3Months()^ENDRLQUERY

---

Basically you check for all users with approver_user role and do a related query on the approval table and check that you cannot find a record from last 3 months which has been approved/rejected.

[Amit Gujarathi]

Hi @Enabler ,

I trust you are doing great.

Please find below script for the same.

// Set the role name for which you want to check the approval status
var roleName = 'Approver';

// Get the user's sys_id for whom you want to check the approval status
var userId = gs.getUserID();

// Get the current date and time
var currentDate = new GlideDateTime();
currentDate.subtract(90 * 24 * 60 * 60); // Subtract 90 days to get the date from 3 months ago

// Query the sysapproval_approver table to find all approvals for the user's role
var gr = new GlideRecord('sysapproval_approver');
gr.addQuery('approver', userId);
gr.addQuery('state', 'requested');
gr.addQuery('sys_updated_on', '>=', currentDate);
gr.addJoinQuery('sys_user_has_role', 'sys_user', 'sys_id', 'user', 'sysapproval_approver.approver');
gr.addJoinQuery('sys_user_role', 'sys_user_has_role.role', 'role', 'sys_user_role');
gr.addQuery('sys_user_role.name', roleName);
gr.query();

// If there are no approvals for the user's role in the last 3 months, log a message
if (!gr.hasNext()) {
    gs.info('User does not have any ' + roleName + ' approvals pending from the last 3 months.');
}

Please Mark My Response as Correct/Helpful based on Impact

Regards,

Amit Gujarathi