When are records written to sys_history_line table?

Andy105 · ‎04-14-2020

Hi

I need to retrieve all of the customer visible work notes on a CSM case record (and probably ITSM incident too).

I've done this by querying the sys_audit and sys_history_line tables, but our System Architect has said this needs to stop.

From what I can understand, its bad practice to query sys_audit, given the table has the potential to be huge (unlikely in our case), whereas sys_history_line is a 30 day subset, so is ok to query.

Questions:

1. If sys_history_line is a subset, at what point do the work note records get written and deleted?

2. If I'm looking for work notes related to a case that was closed 12 months ago, will sys_history_line contain all of the work notes, or do they need to come from another table?

Thank you !

Tanvi Jain · ‎04-15-2020

Hi,

Please find the below links that might be helpful:

The docs link above explains the creation of records in sys_history_line table.

Please mark reply as Correct/Helpful, if applicable. Thanks!

Regards,
Tanvi

Andy105 · ‎04-16-2020

Thanks Tanvi!

I'd already found the second link you listed, but found it to be vague and lacking detail. The first link helps to explain the process a little more.

At the moment, I'm querying sys_audit for records related to my record of interest, then querying sys_history_line for anything it contains too (sys_history_line contains display values, whereas sys_audit contains sys_id only), then comparing. This can obviously be inefficient in a lot of situations, but currently the most efficient method I can find which doesn't risk missing records.

Is there a standard method for requesting the history records via script, which builds a new history set if not up to date or not available? This must be done internally (as it is documented), but is the method it uses exposed to developers?

Andy

Tanvi Jain · ‎04-16-2020

Hi Andy,

Please find the below links, looks like we need to generate it manually:

Regards,

Tanvi