Figure out table in which Field changes date and time recorded

[Nandhinisri]

Hi,

Whenever a state in problem is changed, the date and time in which it was changed is shown in activities tab. But I want to know in which table it is stored in order to execute that in a script. I am attaching the ss for reference. Any suggestion would help.

TIA

[rambo1]

Hi @Nandhinisri 

This data is stored in sys_audit table, its not recommended to query sys_audit table.

Below is sample code to get the info :

// Create a new GlideRecord on the sys_audit table
var grAudit = new GlideRecord('sys_audit');

// Add required filters
grAudit.addQuery('fieldname', 'state'); // Problem state field
grAudit.addQuery('documentkey', '<Problem sys_id>');
grAudit.orderByDesc('sys_created_on');

// Query the sys_audit table
grAudit.query();

// Loop through the results and print them to logs
while (grAudit.next()) {
var oldStateValue = grAudit.getValue('oldvalue'),
newStateValue = grAudit.getValue('newvalue'),
changeTime = grAudit.getValue('sys_created_on');

// Log the state change details
gs.info('State changed from ' + oldStateValue + ' to ' + newStateValue + ' at ' + changeTime);
}

Also you can get this info by navigating to audit history of the record :

Right click on banner >> history >> LIST

you should be able to find all details

[Nandhinisri]

Actually I got that. It is stored in sys_history_line table. But I am stuck in the logic part of writing the code. I have the updated time field. I need the exact time of when the state was changed from Root cause analysis to Fix in progress. If I use sys_history_line.update_time, which one the system will consider?

[Ankur Bawiskar]

@Nandhinisri 

yes it's stored in sys_history_line and sys_history_set

to get the exact time use this filter

Also the Update time it shows on form/list is as per logged in user's timezone

If you open that record and see the value it will always be in GMT

Label - State

Old - Root cause analysis

New - Fix in progress

Use the Update time

So the value you see in activity filter is as per logged in user's timezone

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

[rambo1]

HI @Nandhinisri 

I checked in background script with below code:

var gr = new GlideRecord('sys_history_line');

gr.addEncodedQuery('labelSTARTSWITHstate^old_value=103^new_valueSTARTSWITH104');

gr.query();

if(gr.next())

{

    gs.print(gr.update_time);

    var dat = new GlideDateTime(gr.update_time);

    gs.print(dat.getDisplayValueInternal());

}

When we use the value of 'update time' it returns time in GMT , so if you want it in user's time zone i.e exactly whats present on activities tab 

make it glidedate object using below line :

var dat = new GlideDateTime(gr.update_time);

 dat.getDisplayValueInternal()); // This will display time in user time zone.

Please mark this as correct/helpful if this helped.