I’m having an issue where a client wants a report on every change to every field. I know that’s not possible out of the box. I was thinking about the audit log. However that means making every single field in every module an “audit” field. (Their projects module has like 100 custom fields! alone). I’m thinking this would be disasterous for speed on record save and also quite quickly grow the datbase to an unamanageable size.
Anyone ever done anything like this? Issues? Problems? Ideas?
Probably would be a smart idea to implement a logic hook which checks the fields that are changed and creates a bulk insert query in the audit log table
Skip the fields that have audited checked to avoid duplicate inserts ( or uncheck audited for every field
You can do a global hook that will run on all modules and then maybe the thing you need to think about is filtering out certain modules that you don’t want to run on. Like background admin modules for example or you could just check for modules that have audit enabled
What would be an example of global hooks? I’m familiar with module hooks, like before save or after save etc, but I don’t see any application level hooks that do this. What were you thinking?
In custom/modules/logic_hooks.php you can also create before and after save hooks. I would say those will be called on the save for all modules. Don’t have the time to test it rn but i think I’ve done something similar in the past
I just tried at my local development system. It is working to have audits for every field of every module. The code is not thoroughly tested but creates entries in the audit tables and shows on audit screens.
Made a logic entry in the custom/modules/logic_hooks.php with this $hook_array['after_save'][] = Array(1, 'Log_Change', 'custom/modules/AllModulesLog.php','AllModulesLog', 'updateAudit');
Added a class file at custom/modules/AllModulesLog.php with the following
code
<?php
if(!defined('sugarEntry') || !sugarEntry){
die('Not a valid entry');
}
class AllModulesLog{
public function updateAudit(SugarBean $bean, $event,$args){
global $db;
if(!empty($bean->fetched_row)){
$changed_fields = array();
foreach ($bean->field_defs as $field => $properties){
if (isset($bean->$field) && $bean->fetched_row[$field] != $bean->$field) {
$changed_fields[$field] = $properties;
}
}
$bean->audit_enabled_fields = array();
$auditDataChanges = $db->getDataChanges($bean, array_keys($changed_fields));
if (!empty($auditDataChanges)){
foreach ($auditDataChanges as $change) {
$db->save_audit_records($bean, $change);
$bean->fetched_row[$change['field_name']] = $change['after'];
}
$bean->createdAuditRecords = true;
}
}
}
}
Tested for few fields on accounts, contacts , opportunities modules. I am not sure about the performance. May be we can have a config value in config_override.php to enable/disable the entire module fields audit.
what’s the business requirement behind it, if I may ask?
I had something similar before in a project - the target was time series reporting + trend reporting.
What I’ve done was simply to trigger on all updates and take the whole bean and store it inside a MongoDB.
That has the huge advantage, that the schema is flexible (even if there are field changes in the CRM later on) and the whole reporting was run on the Mongo / DWH.
Alternatively, I’ve had another project where we just use Suite with plenty (not all, but plenty) of audited fields and there we have issues with huge DB table sizes, backups are incredibly slow, Restore over the network practically impossible with several hours downtime etc.
