What is the best way to find sys_audit record for sys_journal_field

[andriyko]

What is the best way to find sys_audit record for sys_journal_field via REST API?

For example I have this sys_journal_field record:

{u'element': u'work_notes',

  u'element_id': u'c2cfdfed0fa58200197f0cbce1050eb1',

  u'name': u'task',

  u'sys_created_by': u'admin',

  u'sys_created_on': u'2015-07-29 15:12:37',

  u'sys_id': u'b6efdfed0fa58200197f0cbce1050e9c',

  u'sys_tags': u'',

  u'value': u'work note #1'}

And sys_audit record:

{u'documentkey': u'c2cfdfed0fa58200197f0cbce1050eb1',

  u'fieldname': u'work_notes',

  u'internal_checkpoint': u'14eda5f54770000001',

  u'newvalue': u'work note #1',

  u'oldvalue': u'JOURNAL FIELD ADDITION',

  u'reason': u'',

  u'record_checkpoint': u'1',

  u'sys_created_by': u'admin',

  u'sys_created_on': u'2015-07-29 15:12:37',

  u'sys_id': u'72efdfed0fa58200197f0cbce1050e9c',

  u'tablename': u'incident',

  u'user': u'admin'}

The query would look like:

documentkey=c2cfdfed0fa58200197f0cbce1050eb1^fieldname=work_notes

I cannot use "newvalue" as max length of "value" column in sys_journal_field table is 4K, also "value" is not unique.

Is it safe to use "sys_created_on" field? In both records the time stamp is the same.

But it is generated by the system.

Are there chances that time stamp of sys_journal_filed and sys_audit records will differ?

[coryseering]

Hi Andriy,

I would not recommend querying sys_audit at all. That table can get very large, and querying- even when limiting to a given document key- can take a long time. This is true for sys_journal_field as well- those tables get quite large.

There is an entire caching mechanism for audit data which is used for things like the activity formatter- see sys_history_set and sys_history_line. Those tables exist specifically to reduce the amount of times that sys_audit is read from for active records.

Why do you need to get the sys_audit record based on a journal field? If this is data you feel you will be accessing frequently, you may want to store the relevant metrics in a different table, and only write to that table when your conditions are met. For example, you could set up a business rule to collect data on Change Requests whenever someone submits a Change form, and record only the pertinent information. You could then query your new table using the REST API, and totally avoid any of the problems with large sys_audit datasets.

If your design requires querying sys_journal_field and sys_audit, I suggest taking a look at re-architecting it so you can collect the data you need upfront, rather than diving into some of the largest tables in the system.

[andriyko]

Cory, thank you for your recommendations. Appreciate your time.

I am aware about   sys_history_set and sys_history_line tables and I know that they are used as a caching system (tables rotation etc). The activity formatter (Activity group fields on Incident details page) uses these tables to display incident's activity/history including comments and work notes. But I do not have good understanding how exactly fomatter queries those tables (looks like the activity.xml is a secret feature of SN).

In my case I have to work with sys_journal_field and sys_audit tables anyway.

[coryseering]

I understand design constraints. You gotta do what you gotta do, though I encourage you to look into collecting the information you want to query at insert and/or update time, rather than going back through the sys_audit history.

That said- yes, you'll need to query on document_key and sys_created_on, and sort by record checkpoint if multiple updates happen within the same second. There isn't a foolproof, easy way to link an individual sys_journal_field entry to a sys_audit entry, as far as I'm aware.