Show the first responder of the ticket using the sys_history_line

[Rairai31]

Hi,

I have a requirement to show who moved the ticket from New. I have created a new field, u_first_responder. If a user changes the state from New to any state, user's name should appear in the field. I also created a BR with the following script but I cant seem to populate the first responder field. 

When: before

Script:

(function executeRule(current, previous /*, gs*/) {

    var historyGR = new GlideRecord('sys_history_line');

    historyGR.addQuery('field', 'u_incident_state');

    historyGR.addQuery('set.id', current.sys_id);

    historyGR.orderByDesc('sys_created_on');

    historyGR.query();

    if (historyGR.next()) {

        // Set the "First Responder" field with the user_name value

        var updatedByUser = historyGR.user_name;

         current.u_first_responder = updatedByUser;

    }

})(current, previous);

My assumption is because of this - historyGR.addQuery('set.id', current.sys_id); Can anyone help me on this? Thank you very much.

[Maik Skoddow]

Hi @Rairai31 

the table sys_history_line cannot be used for this purpose as its contents are generated automatically only in case a user opens a record form. The reason is to speed up the overall process as extracting the data from the sys_audit table would take too long.

But there is a better approach using the HistoryWalker API. See the following post for more information: https://developer.servicenow.com/blog.do?p=/post/historywalker/

Maik

[Mark Roethof]

Hi there,

As Maik already mentioned, using the sys_history_set and sys_history_line tables for this is not a good usage. It might work for a few records if someone already triggered the generation of these, those for most records... there won't be any sys_history_set and sys_history_line present.

I do wonder though, why would you query such a table? This is about populating a custom field with a certain value? Why not populate that value upon when the state of the record is changed immediately? Than you can just populate that field immediately with whom, instead of quering other tables or using an API or whatever. Sounds unnecessary.

[Rairai31]

I appreciate all your responses. Yes, I was looking the other way around. Thank you very much.