What is the best way to audit who has viewed a record in a table?

[miriamberge]

We want to track (on a fairly large table) who has viewed the details of a record.  Essentially this would be an audit for sensitive data stored in the system.  We would not want to restrict the user's access to the table but we would want to be able to provide details if someone came to us and asked who looked at the record.  Ideas? 

[Mwatkins]

I like Sanjiv's idea of a custom table.

While I also like his idea of an onLoad script, that would mean that this audit would basically be triggered by client-script that lives in the browser. If you have security concerns that might not be the best solution.

Another thing that comes to mind is the situation where someone is looking at the record via a REST API or some type of UI construct that doesn't support an onLoad script (Agent Workspace, ServicePortal, etc).

Another idea would be to use a before/display business rule. These run before a form is displayed. Object values of "current" can be read but not changed and you could write to your custom audit table here. Again, I don't think display business rules cover every single scenario where someone "views" a record.

What about lists? Do you want to track when someone views these records as part of a list?

Here's a couple crazy ideas:

1. What about a record level ACL that somehow triggers a write to the custom table? ACL's would apply in all circumstances and can't be circumvented and they have access to the "current" object. The trick here is that you really would need your code to be incredibly fast or you could risk major performance issues.
2. Perhaps you could delegate the processing to a queue somehow.
3. Or maybe you can keep it simple and just make sure that your INSERTs are fast enough against the custom table that there is no degradation. Use GlideRecord.setWorkflow(false) to avoid overhead of extra code processing as a result of your insert. You'll definitely want to make sure you avoid duplicates to keep the table small. If you had a DB index on table+record_sys_id+user_sys_id as a single string combo and only inserted unique entries maybe that would do the trick.

Anyway, good luck and be careful on this one.

[sachin_namjoshi]

Custom table is expensive with new licensing model.

Customers need to pay for creating custom table in global scope.

Do you have any other solution apart from creating custom table?

Regards,

Sachin

[Mwatkins]

Hm... the OP didn't say not to use a custom table. Is that a requirement, Miriam?

I suppose you could do something like trigger an automatic update on the record being viewed before it gets viewed (ACL or before/display business rule). If the table is audited then the update of the record would be stored in the out-of-box audit table. There is a bit of danger there in terms of how many audit entries would be associated with the records. That might get detrimentally large but audit table is pretty resilient to size.

Or what about using the sys_journal_field table. You could add an entry to that table and check for duplicates first. "Viewed by so-and-so".

[sachin_namjoshi]

Well, querying sys_audit table is bad idea and and it will cause performance issues due to every growing size of this table.

Also, sys_journal_field  is also huge table like sys_ui_navigator_history table.It will not be good idea to use sys_journal_field  table since there will be 2 transactions for each update for record viewed in this table. 1 for inserting record and another for querying viewed record history.

Regards,

Sachin

[Mwatkins]

Yeah, I agree the size of those tables would be a big performance concern. You would want to be very careful how you access them when needed.

From the OP's description it didn't sound like they would be querying things very often, they just want to have the data there in case they ever wanted to query it. Ultimately the sys_audit table is meant to track audit information, so I think saying, "querying sys_audit table is bad idea and and it will cause performance issues" is a little too broad of a statement. If that were the case, what good is the audit table?

But, yes, you are correct that they would want to be very careful about how much data goes into the audit table or sys_journal_field as you would potentially get a bunch of duplicate views from the same users and the size could easily grow too rapidly and too large. There are many documented PRB's where having, say, 70k audit entries per record can cause the Activity Formatter to run very slowly (it works off the History Set feature that in turn gets its data from the audit table directly). Activity Stream is being rewritten in Orlando to perform well even with a very high number of audit records per document. But you'd still need to enact some safety measures to ensure you avoid performance issues.

Both the sys_audit and sys_journal_field tables are optimized to return data efficiently when queried by certain fields; documentkey and element_id respectively. Any data retrieval strategy should use these fields and only be done on an as-needed basis versus automatically being surfaced by something like a related list.

In terms of avoiding excessive entries you would need some kind of duplication protection. Again that would need to leverage the documentkey/element_id fields to run efficiently and you'd need to test at scale to ensure good performance.

I do hope to be helpful and factual in my posts.