what's the difference between update time and created fields in sys_history_line table?

[Anushree Randad]

What is the difference between updated time and created fields in sys_history_line table? I am trying to pull a report which will show me field value from sn_compliance_control table which were changed after 12/6/2016. Not sure which field to use from above 2? I assume when there is any change in the field value, a record gets created in history table so is created field more relevant or updated time (Not even sure why do we have updated time apart from updated and i don't see any value in updated field)? Updated field is blank so can't use that. Can someone help me with this as soon as possible?

[jleslie2007]

Hi Anushree,

I would use the updated time only because that's what it is designed for. I don't think you would see any problems if you went by creation date, though.

From what I've seen, they are one in the same. All updates to fields on audited tables create new records on the sys_history_line table. This table contains all of the sysauto fields by default, which is why you see the update and created fields. I don't think there's a particular reason, they just come out of box.

I hope this helps!

Jeremy Leslie
Virteva Servicenow Technician

[Anushree Randad]

Thanks for your reply Jeremy. I thought the same that there won't be any difference between updated time and created dates but I see a difference and that's why I was confused and asked this question. But yes, maybe updated time will work.

[drjohnchun]

Why not go directly to the source and use the Sys Audit [sys_audit] table? sys_history_line is a partial copy generated from sys_audit on demand as described in History Sets - ServiceNow Wiki. You can filter sys_audit by tablename=sn_compliance_control and narrow down the date range to see the changes to field values that you need. The table has only one datetime field sys_created_on, which is when the value changed, so there's no confusion.

I've also posted some other considerations for audit trails at Re: ServiceNow auditing.

Hope this helps.

Please feel free to connect, follow, mark helpful / answer, like, endorse.

John Chun, PhD PMP

Winner of November 2016 Members' Choice Award

[Anushree Randad]

Hi John,

Thanks for your reply. I used sys_history_line table because it has 'set' field which tells me for which 'control' from sn_compliance_table the field changes are for. I started my first report on audit table but figured out I cannot list the control names from this table and that's why I went for history line table.

Thanks,

Anushree