Accessing The "is not one of" Operator For User Sys ID Queries

[JosephW1]

Hello,

I am trying to run the following query:
"Opened by.Sys ID" "is not one of" "[sysid1],[sysid2],[etc]"

However, when choosing the operator, "is not one of" is not an available option.

I do have "is one of" as an option. ALSO, if I manipulate the url to impose the "is not one of" upon the query by changing the "opened_by.sys_idIN" to "opened_by.sys_idNOT%20IN", the query works and returns the expected "is not one of" results. So, I know that this operator would work on this query if it was available. Manipulating the URL is not a proper method of end-user access to this feature, though, nor is it usable in reporting.

Update 4.19.2021: That URL manipulated query can be pulled into the report designer, from a table query, by right-clicking a column header and choosing "Bar chart" or "Pie chart".

I have checked the "Operators available for filters and queries" document and it states that this operator is available for choice fields containing strings or integers, and I find that it's typically available when building conditions for fields that have choices defined.

Is there a way for me to have the "is not one of" operator available within the drop-down list of operators for this query?

Creating a dynamic filter won't work for my needs, as I need to be able to pass varying parameters on to the query.

Thank you very much for taking the time to read this question!

Kind Regards,
Joseph

[JosephW1]

This list/filter will be used in reports, so it will likely mostly be used by the stakeholders.

By a check box on incident, you don't mean a user input, correct? We find that to be too inaccurate given inconsistent user participation on checking said checkbox.

Do you mean something similar to creating a new Incident Metric definition to capture a list of all of the groups that the "Opened by" user was a part of when the incident was created? However, this would not work, as the "Value" field does not have "is not one of" as an available operator! We would be back in the same situation. ugh, lol.

Is there really no way to manually override and shoe-horn an operator into the list of available operators for any given field? I already confirmed via url manipulation that the operator would work here if I could just make it an option.

[ggg]

suggestion:

filter by user.name

in incident, for example, i can filter on

caller ... does not contain ... 'David Miller'

[JosephW1]

Thank you for all of your time ggg.

Unfortunately, I checked the user.name field and it does not list "is not one of" as an option. The User.Sys ID field already has the contains & does not contain operators anyways.

Unfortunately, I believe that the "contains" and "does not contain" operators can not handle strings of multiple parameters. I just tested it and it did not work for filtering multiple agents at a time.

[JosephW1]

I am still hoping to find a solution to this issue. Thank you to everyone who has contributed thus far.

[Madhuri23]

Hello @JosephW 

As I had a similar requirement of using 'Sys ID' does not contain <comma seperated sys_ids> in my code, I tried implementing a solution using addQuery instead of encodedQuery (as even I could not find something as 'NOT IN'/'Does not contain' being supported for sys_id field) using Encoded Query on ServiceNow table.

Below is the piece of code that served my requirement of getting the Users (sys_ids) which were not part of any of the Lead Group(Group Name contains 'lead') -

var leadGrpMem = [];
var NonleadGrpMem = [];
var grp = new GlideRecord('sys_user_grmember');
grp.addEncodedQuery('group.nameLIKElead');
grp.query();
while (grp.next()) {
leadGrpMem.push(grp.getValue("user"));
}

var grMember = new GlideRecord('sys_user_grmember');
var LeadGrpUserSysID = leadGrpMem.join();
grMember.addQuery('user.sys_id', 'NOT IN', LeadGrpUserSysID);
grMember.query();
while (grMember.next()) {
NonleadGrpMem.push(grMember.getValue("user"));
}
return "sys_idIN" + NonleadGrpMem.join();

The code worked as expected for me as it returned only the users(sys_ids) which were not in the array 'LeadGrpUserSysID' (users from the Lead Group).

Please feel free to try this solution and mark it as 'accepted' if it resolves your query/issue too.

Good Luck!

Regards

Madhuri Bhawnani