Script - Query sys_history_line | Not matching change_request.sys_id and set.id

[hanaphouse]

var gr = new GlideRecord("change_request")
gr.query();
while(gr.next()) {
    var line = new GlideRecord("sys_history_line");
    line.addQuery('set.id',gr.sys_id);
    line.setLimit(1);
    line.query();
    if(line.next()){
    gs.print('CHG: ' + gr.number.toString()  + ', Set ID: ' + line.set.id.toString());
    }
else{gs.print('CHG: ' + gr.number.toString()  + ', Set ID: ' + line.set.id.toString());
}
}

What could be wrong in this script? What I am trying to do is validate if all CHG number has a corresponding sys_history_line entry.

When I try to run this script, it doesn't match the set IDs.

[Christian_]

Hello, 

I suspect this is the reason why this is not working as you expect:

• The system automatically generates History Set and History records as needed from the Audit table when a user either creates a record or requests its history.

Differences Between Audit and History Sets

[AshishKM · Accepted solution]

Hi, 

Open any change and run the History->Line command and generate the record in sys_history_set table until the set table will not have any history record. 

Refer the below links 

Scripts: GlideRecord sys_history_set / sys_history_line / sys_audit

what is the exact difference or use of [sys_history_set] and [sys_history_line] tables with a simple...

Thanks,

Ashish

[hanaphouse]

This pointed me to GlideHistorySet() which triggers a record in [sys_history_set] which makes the CHG sys_id and other CHG changes to be visible [sys_audit_history_line].

new GlideHistorySet('<table>','sys_id').generate()

[Brad Bowman]

The id field on sys_history_set has the type of document id, so it needs the dependent field, table in this case, in the query also in order to match to the change request sys_id.  Add this line to your GlideRecord query and you will see one successful log. and many following the else path - though you wouldn't know it with your log statements being the same.  This follows the logic of your query, but I don't think that's what you're really looking for here as it won't be obvious if one change record doesn't have a history, but this will get you further along.

line.addQuery('set.table', 'change_request');