How to check a condition in addEncodedQuery()

Ajith7 · ‎07-12-2019

I want to send a notification to users who are inactive for 25 days and the role should be itil and the group should not be custom group(group name). I have checked the condition for role but I could not put a condition to check the group name in addEncodedQuery(). Can anyone resolve this issue.

gr.addEncodedQuery("roles=itil^active=true^last_login_timeRELATIVELE@dayofweek@ago@1");

I wanted to put a condition for group

eg:

group not equal to test_group.

Can anyone help me to put the correct condition

Lukasz Bojara · ‎07-12-2019

How are you using the result? If you want to use the user's sys_id remember that when you query the sys_user_grmember table then if you will use gr.sys_id it will give you the sys_id of a connection record so it will be not the User's sys id.

To use the user's sys_id you should do gr.user.sys_id

In overall if you need to access user fields when you query sys_user_grmember table you need to dot walk to this field.

e.g.

var gr = new GlideRecord('sys_user_grmember');
gr.addEncodedQuery('user.last_loginRELATIVELT@dayofweek@ago@25^user.roles=admin^user.active=true^group.name!=Team Development Code Reviewers1');
gr.query();

while (gr.next()){
 gs.print(gr.user.sys_id); // print the user's sys_id
}

View solution in original post

Akshata jamdar · ‎07-14-2019

you can find the relation between users and groups in the table [sys_user_grmember] table you can try with this table name.

Please mark it as correct if it is helpful

Thanks and Regards

Akshata

ServiceNow Developer

Ajith7 · ‎07-14-2019

Thank You Akshata for helping. My Issue is ressolved

Kristoffer Mon1 · ‎07-14-2019

First try setting up a query in the sys_user_has_role table like so:

Notice the "granted by" field? You can put conditions on the group field, say for example, only groups created by AD sync. In my case I use the "source" field since this is what we use to store the AD distinguished name.

Righ-click query breakcrumb to "Copy Query" then copy query into a GlideAggregate query like so:

var gr = new GlideAggregate("sys_user_has_role");
gr.addEncodedQuery("role=282bf1fac6112285017366cb5f867469^user.last_login_timeNOTONLast 30 days@javascript:gs.beginningOfLast30Days()@javascript:gs.endOfLast30Days()^granted_by.sourceISNOTEMPTY");
gr.groupBy('user'); 
gr.query();
while (gr.next()) {
   gs.info(gr.getDisplayValue('user'));
   //send notificaton by event trigger
}

Within the while loop you can generate notifications for all users matching the query.

Ajith7 · ‎07-14-2019

Thankyou Kristoffer. I got the solution.

Ajith7 · ‎07-14-2019

Thankyou Everyone. I got the solution.