Close hung Requests without sending out email

[chrisn_]

Hello everyone,

Unfortunately a few of our key workflows were not done correctly. The tasks were closed and the RITM's were closed but the REQ's remain open. We now have 2100 REQ's that need to be closed and I would like to pick your brains for a method to do so without sending out the 2100 emails that ServiceNow will want to send. In the mind of the requester everything is complete so they are not waiting for any further word. Would this be possible with a scheduled job or do we need to do something drastic such as turn off email during off hours, close the REQ's and then clear the email that it creates before turning it back on?

[Michael Ritchie]

Chris your script above is definitely targeting all requests and its child items.  Is there a timeframe on when this happened that you can provide?  What needs to be done is to loop through all the closed req items looking where the request is active and then close the request.  But I would imagine you may have 1000s of closed req items so would prefer to include a date range in the query.  Please advise.

[Michael Ritchie]

Chris here is an example script which should work.  Please set the startDate and endDate's to your date range:

// Please set the start and end dates
var startDate = "2018-01-01";
var endDate = "2018-08-01";

// Query for inactive req items where its request is still active
var reqQuery = "active=false^request.active=true"
// Query based on a date range
reqQuery = reqQuery + "^opened_at>javascript:gs.dateGenerate('" + startDate + "','00:00:00')^opened_at<=javascript:gs.dateGenerate('" + endDate + "','23:59:59')"
var reqItem = new GlideRecord("sc_req_item");
reqItem.addEncodedQuery(reqQuery);
reqItem.query();
while(reqItem.next) {
	var req = new GlideRecord("sc_request");
	req.get(reqItem.request);
	req.state = 3;
	req.request_state = "closed_complete";
	req.active = false;
	req.setWorkflow(false); // prevent notifications from going out
	req.update();
}

[chrisn_]

Hi Michael,

I do not have an exact date range. Multiple developers did not follow best practices so certain items have not been closing almost from the beginning and incredibly no one ever noticed until now... That being said I can say all requests with closed RITM's should be closed regardless of date at this point. That is why I went with what I'd made, but it also closed requests that had open RITM's which was the error I was trying to ferret out.

[Michael Ritchie]

Chris ok, then this should work, same code minus the date query:

// Query for inactive req items where its request is still active
var reqQuery = "active=false^request.active=true"
var reqItem = new GlideRecord("sc_req_item");
reqItem.addEncodedQuery(reqQuery);
reqItem.query();
while(reqItem.next) {
	var req = new GlideRecord("sc_request");
	req.get(reqItem.request);
	req.state = 3;
	req.request_state = "closed_complete";
	req.active = false;
	req.setWorkflow(false); // prevent notifications from going out
	req.update();
}

[chrisn_]

Hi michael unfortunately this is not actually working for me. I am not getting an error in the system log, but neither are any requests being closed. Looking at the code it should work, but as far as I can tell it is not pulling anything in for that query of the sc_req_item table or sc_request table.

By any chance would it work better to use a nested query and next instead of the while? The code you provided makes sense to me, but unfortunately it is not jiving with my instance.