Querying sys_history_line tables

[poyntzj]

I have a requirement to query against the sys_history_line table and retrieve some information.

I had a request to display the "fulfilment" date on a Portal record.

On checking a couple of records, I can query the sys_history_set and then the sys_history_line

I soon realised this was not great for the portal so I have added a new field to the sc_request and sc_req_item tables.

However, now I am being asked to back populate the data

i have had a look at pulling from sys_audit and from the sys_history_line tables.

the later are better for my needs.

I am however finding that when I run a Background Script to get some data I am offten getting a lot of null information for the field I am after.

Script: REQ0015788 : b76ef6ec6f1782402a3e9accbb3ee4ac : 2015-10-21 10:03:36
*** Script: REQ0015776 : 43b86b0d6f6346007115ca727d3ee404 : 2015-11-04 12:40:15
*** Script: REQ0015763 : 6d2d55df6f5f4200b872129e5d3ee424 : 2015-10-29 16:01:48
*** Script: REQ0015756 : 22db3cd76f5f4200b872129e5d3ee43b : 2015-11-05 10:49:29
*** Script: REQ0015750 : d05479956f970200b872129e5d3ee47a : 2015-11-05 10:49:30
*** Script: REQ0015749 : 421179d16f970200b872129e5d3ee4c9 : 2015-11-05 10:49:32
*** Script: REQ0015744 : c2d7c15d6f570200b872129e5d3ee439 : 2015-10-29 16:24:22
*** Script: REQ0015737 : cc16a0496f930200b872129e5d3ee435 : null
*** Script: REQ0015605 : f35b82dc6f5f42402a3e9accbb3ee4eb : null
*** Script: REQ0015600 : 74f84a5c6f5f42402a3e9accbb3ee492 : null

If I now go into the record REQ0015744 and come straight back out and run the Backgroun Script again, I now see the information

*** Script: REQ0015788 : b76ef6ec6f1782402a3e9accbb3ee4ac : 2015-10-21 10:03:36
*** Script: REQ0015776 : 43b86b0d6f6346007115ca727d3ee404 : 2015-11-04 12:40:15
*** Script: REQ0015763 : 6d2d55df6f5f4200b872129e5d3ee424 : 2015-10-29 16:01:48
*** Script: REQ0015756 : 22db3cd76f5f4200b872129e5d3ee43b : 2015-11-05 10:49:29
*** Script: REQ0015750 : d05479956f970200b872129e5d3ee47a : 2015-11-05 10:49:30
*** Script: REQ0015749 : 421179d16f970200b872129e5d3ee4c9 : 2015-11-05 10:49:32
*** Script: REQ0015744 : c2d7c15d6f570200b872129e5d3ee439 : 2015-10-29 16:24:22
*** Script: REQ0015737 : cc16a0496f930200b872129e5d3ee435 : null
*** Script: REQ0015605 : f35b82dc6f5f42402a3e9accbb3ee4eb : null

arrgh Is there any way I can make this work - it will be a background task run once for the sc_request and sc_req_item tables

It is as if I need to open and inspect each record or somehow force the history tables to work

I did try a query against the sys_audit table and it was seemingly having a similar issue

any ideas would be gratefully received

[bernyalvarado]

Hi Julian,

Based on the data you shared it appears that you're not getting null for REQ0015744 on none of the background scripts. In fact, they appear to have the same time.

Would you mind rephrasing your question so that we can have a better understanding of the problem you're facing?

Thanks,

Berny

[poyntzj]

Hi Berny

If I run a query that will take the sys_id of the REQ record, look at the sys_history_set.   I get the relevant information i need and then try to query the sys_history_line table to return the latest close_code entry

I need to populate a field on the REQ table

When I run the query I often find lots of null entries

If I go into the record, at the bottom we see the Activity states - including the one I am searching for

If I now got back and run my query again, I now get to see the data from the sys_history_line.

It is as if I need to force the line to load to do this.

I did try against the sys_audit table and got a similar problem

[bernyalvarado · Accepted solution]

Hi Julian, I now understand what you're looking for. Indeed it's odd how records get added to the audit / history tables. The good news is that there is a work around.

On your case you can create an Insert / Update OnAfter business rule in the change_request table and have a script like the following:

new GlideHistorySet("change_request", current.sys_id).generate();

That will make the trick

Thanks,

Berny

[davidsizu]

This function is amazing, thanks for sharing the info!