Need to populate custom 'u_closed' Date/Time field on Incident using Activity history

[DevYadav]

Hi community,

I have a requirement to populate a custom Date/Time field u_closed on the Incident table. I want this field to capture the exact timestamp when the state changes to "Closed" (state = 7), matching the time shown in the activity stream where the state is updated. This value should appear in the Incident list view. I’m looking for a fix script that can update existing historical records by pulling the correct close time from activity or history (e.g., sys_history_line or any better method), and I would also like to know the best approach to ensure this continues to work for future state changes. Please guide with a working script or best practice.

Thanks.

[Ankur Bawiskar · Accepted solution]

@DevYadav 

2 things you need to do

1) for future changes you can use before update business rule

Condition: State Changes to Closed

Script:

current.u_closed = new GlideDateTime();

2) Write a fix script to pick the exact time from sys_history table

Something like this

For e.g. I am fetching history for incident number 'INC0010016' and getting when State changed to some value from empty. you can enhance it

var incident = new GlideRecord('incident');
incident.addQuery('number', 'INC0010016');
incident.query();
if (incident.next()) {
    var history = new GlideRecord('sys_history_set');
    history.addQuery('id', incident.getValue('sys_id'));
    history.query();
    history.next();
    var auditH = new GlideRecord('sys_history_line');
    auditH.addQuery('set', history.getValue('sys_id'));
    auditH.addEncodedQuery("label=State^newISNOTEMPTY^oldISEMPTY"); // new is not epmty and old is empty
    auditH.query();
    if (auditH.next()) {
        incident.u_closed = new GlideDateTime(auditH.sys_created_on);
        incident.setWorkflow(false); // avoid triggering any BR, flow, notification etc
        incident.update();
    }
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Shraddha Kadam]

Hello @DevYadav ,

We have "Closed" field on the incident table. So you can map this field with the u_closed field. You can use below script for updating the field value -

var closedDate = '';
var gr = new GlideRecord('incident');
gr.addEncodedQuery('active=false^state=7');
gr.query();
while(gr.next()){
	closedDate = gr.closed_at;
	gr.setWorkflow(false);
	gr.autoSysFields(false);
	gr.update();
}