Retrieve date and time for FREQ when status changed to completed

[Akshaykhare]

I want to retrieve date and time of FREQ records when the status of FREQ changed to "Completed". I know I can retrieve it from sys_history_set  and sys_history_line table. But the issue is I want this date and time for thousands of record and I couldn't find a way to do this in a bulk.

[Ankur Bawiskar · Accepted solution]

@Akshaykhare 

If your table is audited then you can do.

if you have a custom field to store then below sample script you can enhance for your table and field (date/time type)

I am giving example for incident table

if table is audited then you can do this

You can do GlideRecord from following tables,

sys_history_set and sys_history_line

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

var incident = new GlideRecord('incident');
incident.addQuery('number','INC0010016');
incident.query();

while(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_field = new GlideDateTime(auditH.sys_created_on).getDate();
incident.update();

}

}

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

[Akshaykhare]

@Ankur Bawiskar : Thanks it worked👍