Nested Queries

[zschneider]

I'm trying to create a scheduled job that will run daily to add terminated employees to inactive and remove any groups the user may have, similar to what's found here: How do I remove a user from all groups using a script

We use the LDAP code 512 for active and 514 for inactive. I made a query to find all users with a custom field valued at 514. Tested it, works great. Then I created a nested query to delete the current user's groups. This caused the script to delete every user's group, regardless of wether or not their custom field value was 514 or 512. I'm positivie the first query only returns those with a value of 514. So why would the second query in the code below work for both 512 and 514 users?

Can anyone see what i'm doing incorrectly?

cleanAD();

function cleanAD(){
var cleanRoles = new GlideRecord('sys_user_grmember');
var getInactive = new GlideRecord('sys_user');

getInactive.addQuery("u_uaccountcontrol","514");
getInactive.query();
while(getInactive.next()){
cleanRoles = new GlideRecord('sys_user_grmember');
cleanRoles.initialize();
cleanRoles.addQuery("name", getInactive.sys_id);
cleanRoles.query();
while (cleanRoles.next()){
cleanRoles.deleteRecord();
}

}

[Mark Stanger]

Your problem is with this line...

cleanRoles.addQuery("name", getInactive.sys_id);

I think you want this instead...

cleanRoles.addQuery("user", getInactive.sys_id);

[zschneider]

Thanks Mark! I would have spent hours going over the code and missing something that simple.

I'm still curious why a query that I would think would return nothing, since the Name field doesn't exist and wouldn't equal sys_id, ends up returning every single user in the sys_user table.

[Mark Stanger]

That's just the behavior of the GlideRecord query object. I agree that it should work the other way. This has been brought to the attention of ServiceNow development but I don't know what the plans are to address it. At least now you know to look out for it in the future. I usually run my script and log the row count of records before mass updates and deletes just to verify that I'm getting the right result set.

[CapaJC]

If you want, you can create and set the following property to true: glide.invalid_query.returns_no_rows

Then if your query includes a condition on a field that doesn't exist, your query will return no records.

Be vigilant if you set this, however. It could break queries that otherwise might LOOK like they're working perfectly fine right now, if they happen to have a typo in one of the lines. And since we've never shipped the property turned on, I wouldn't discount the possibility that there are out-of-box scripts that would break as well.

While the property may someday be turned on for new instances, it will never be turned on for existing ones due to the risk of customer scripts breaking.

http://wiki.service-now.com/index.php?title=GlideRecord_Server#Controlling_invalid_queries