glide query for user who have roles

[Brian Lancaster]

I'm trying to create a scheduled job that will search the user table for anybody that has a role so I can remove their roles if they have not logged in, in 45 days.   I wan't to search for the user having a role.   Is there any way to do a addQuery(has role) to return everybody who has a any role on the sys_user table?

[Pradeep Sharma]

Hi Brian,

You have to GlideRecord the table "sys_user_has_role" which will have the records if the user has a role.

Please let me know if you have any questions.

[Brian Lancaster]

ok so how do I get both tables in 1 query?   What they want is for me to remove any roles for a user that has not logged in in 45 days.   The last login is in the sys_user table.   I am trying to reduce the number of users the system has to look thought by only looking at the ones who have a role.

[Jamsta1912 · Accepted solution]

Hello Brian,

You can 'dot-walk' over to the user record, either using addQuery or with an encoded query like this:

var uRole = new GlideRecord('sys_user_has_role');
uRole.addEncodedQuery("user.last_login_timeRELATIVELT@dayofweek@ago@45");
uRole.query();
while(uRole.next()){
   uRole.delete();
}

To get the encoded query string, I viewed a list of User Roles, 'sys_user_has_role' records, and added this filter:

and then right-click over the bread-crumbs and choose 'copy query'. This is a really handy way of doing it, because then you don't have to learn the details of syntax like 'last_login_timeRELATIVE@dayofweek@ago@45'. ServiceNow effectively writes the encoded query string for you.

[Brian Lancaster]

Hi Jamie,

This worked great thinks for the explanation with screenshots on who you got the encoded query.