Help with Scheduled Job to autoclose tickets after 3 days in specified state

[William McKiern]

Hey all,

The requirement I have is to create a scheduled job which will close tickets that have been in the "Complete, Awaiting Confirmation" state for 3 days time. I used another thread (Schedule job on Custom table ticket to auto close - IT Service Management - Question - ServiceNow Co...) to get the code, but I can't seem to get it working in my scoped app.   I have a field called "Awaiting Confirmation" that captures the time that a ticket was marked "Complete, Awaiting Confirmation", and I am using that as the base to to calculate three days from. When I "Execute Now" on the scheduled job, nothing changes on the records that match the criteria. Can anyone help decipher what I might be doing wrong?

Here is my code:

function autoCloseTasks() {

    var pn = parseInt(3); //3 days 
    var queryTime = new GlideDateTime();
    queryTime.addDaysUTC(-pn);

    if (pn > 0) {
        var gr = new GlideRecord('x_g_ngr_ariba_inqu_ariba_inquiries'); //replace with table name
        gr.addQuery('state', '11'); //replace with resolved value
        //gr.addQuery('awaiting_confirmation', '<', queryTime);
        gr.query();
        while (gr.next()) {
            gr.state = '10'; //replace with close value
            gr.comments = 'Ticket automatically closed after ' + pn + ' days in the Awaiting Confirmation state by the system.';
            gr.active = false;
            gr.closed_by = gr.resolved_by;
            gr.update();
        }
    }
}

Here are the states in question, Awaiting Confirmation and Closed:

[suvro]

Use the below code

function autoCloseTasks() {

    var pn = parseInt(3); //3 days 
    var queryTime = new GlideDateTime();
    

    if (pn > 0) {
        var gr = new GlideRecord('x_g_ngr_ariba_inqu_ariba_inquiries'); //replace with table name
        gr.addQuery('state', 11); //replace with resolved value
        
        gr.query();
        while (gr.next()) {
		    var LastUpdatedDate = new GlideDateTime(gr.sys_updated_on);
			var diff = GlideDateTime.subtract(LastUpdatedDate, queryTime);
			var days = diff.getRoundedDayPart();
			
			if (days > = pn){
  
				gr.state = '10'; //replace with close value
				gr.comments = 'Ticket automatically closed after ' + pn + ' days in the Awaiting Confirmation state by the system.';
				gr.active = false;
				gr.closed_by = gr.resolved_by;
				gr.update();
			}
        }
    }
}

[Hitoshi Ozawa · Accepted solution]

Hi William,

Is "awaiting_confirmation" a Date/Time in UTC?

Execute the following script to find the format and value of "awaiting_confirmation". The format should be 'yyyy-MM-dd hh:mm:ss'. 

        var gr = new GlideRecord('x_g_ngr_ariba_inqu_ariba_inquiries'); //replace with table name

        gr.setLimit(1);
        gr.query();
        if (gr.next()) {
            gs.info(gr.getValue('awaiting_confirmation'));  // replace with field in the table
        }

Try executing the following in Secript Background to make sure there is any matching record.

var pn = 3;
var gdt = new GlideDateTime();

gdt.addDaysUTC(-pn);
gs.info(gdt.getDate());

var gr = new GlideRecord('x_g_ngr_ariba_inqu_ariba_inquiries'); //replace with table name
gr.addActiveQuery();
gr.addQuery('state', '11'); //replace with resolved value
gr.addQuery('awaiting_confirmation', '<', gdt.getDate() + ' 00:00:00');
gr.query();
while (gr.next()) {
    gs.info(gr.awaiting_confirmation);  // replace with field in the table
}

If the above returns a value, the following should update records.

var pn = 3;
var gdt = new GlideDateTime();

gdt.addDaysUTC(-pn);

var gr = new GlideRecord('x_g_ngr_ariba_inqu_ariba_inquiries'); //replace with table name
gr.addActiveQuery();
gr.addQuery('state', '11'); //replace with resolved value
gr.addQuery('awaiting_confirmation', '<', gdt.getDate() + ' 00:00:00');
gr.query();
while (gr.next()) {
     gr.state = '10'; //replace with close value
     gr.comments = 'Ticket automatically closed after ' + pn + ' days in the Awaiting Confirmation state by the system.';
     gr.active = false;
     gr.closed_by = gr.resolved_by;
     gr.update();
}

[William McKiern]

Hey Hitoshi,

Thank you so much for the reply, this worked! However, it only updated 1 out of the 3 records that should match the criteria. I can confirm that "awaiting_confirmation" is in the 'yyyy-MM-dd hh:mm:ss' format. The second script you posted returns the value of the third record's "Awaiting Confirmation" field, so I can only assume it successfully iterated through all three. When I executed the final script, however, I used a gs.info('Ariba Row count: ' + gr.getRowCount()); to see how many records it found and it said there was only 1 record, and that record did get updated. I will have to do some digging to see why those are getting skipped, but your help has helped me make great progress! I appreciate it!