Code from the creek...: Adding Audit Logs to Reports

My users had a need to query the audit log for various modules in ways that the current built in system would not allow. Now I could create advanced report after advanced report but really I hate making advanced reports. So I added myself a JIRA task to create some way of reporting on the Audit Logs.

All the files are available for download from the GitHUB link at the end of this post.

This was my list of criteria for this project

I need a way to report on any modules audit log
I need a way that allows a manager to create their own reports
That method has to be familiar to them already (this was really me saying I wanted it in the Reports module)
It must not allow editing of Audit Logs in any way
It must be fast over millions of records (some of our audit logs are huge)
It must translate all the raw data back into English
It must allow our system admins to include that new audit_events table data in the reports
It cant break the current Audit code

Pitfalls - READ THIS FIRST

If you have a sandbox then put this on the sandbox first and test it. If you dont have a sandbox then get one and do that try it there first thing.

-- ALSO --

This utility adds a delete field to each audit table, this is just a hard coded requirement of making them into SugarCRM modules. It will not effect how they work or change how the core audit code works.

This utility alters the data in the audit table. It uses the before_value_text and after_value_text fields to store the human readable data. If the audit record is recording an audit of an actual Text Area then it will not be altered in any way. So the net effect is that I use completely unused fields to store data in. The core audit code will ignore this completely and work normally..

You MUST let the scheduled job completely finish updating your audit tables BEFORE attempting to run a report.

The way this thing is written if you go to reports and start a report on one of the audit tables it will run the job that updates the data in your Audit Table. The reason I do this is so that when you run a report you can be sure all the data in the table is up to date. Under normal circumstances, there will be only a few records that need updating and this job will run for only a second or two because the scheduled job should keep the tables processed for the most part. The only records in the audit table that might need processing are the few that were added since the last time the scheduler was run.

While the scheduled job is running you will be able to tell how many records are left to process by watching the sugarcrm.log file. You will see lines like this:

Wed Mar 27 20:00:29 2019 [18907][1][FATAL] Audit: Processing 0 rows from opportunities_audit
Wed Mar 27 20:00:29 2019 [18907][1][FATAL] Audit: Audit Rebuild for the opportunities_audit is complete.
Wed Mar 27 20:00:29 2019 [18907][1][FATAL] Audit: Processing 0 rows from quotes_audit
Wed Mar 27 20:00:29 2019 [18907][1][FATAL] Audit: Audit Rebuild for the quotes_audit is complete.
Wed Mar 27 20:00:31 2019 [18907][1][FATAL] Audit: Processing 1000 rows from tasks_audit
Wed Mar 27 20:00:32 2019 [18907][1][FATAL] Audit: Number of unprocessed rows remaining in tasks_audit: 450172 [1/1000]

You can see on the Tasks audit table there are still 450,172 records left to process whereas on Quotes there are 0.

If you have millions of records in those tables and you have NOT run the scheduled job to completion (which might take several days) then running a report will completely lock up your system.

What kind of module should it be

So since it had to be in reports there were only 2 ways I could do it

Write a single new module that had a view instead of a table and then create that view with something like:
CREATE VIEW audit_tables AS
SELECT * FROM accounts_audit
UNION
SELECT * FROM calls_audit
....

I like making modules with views. It can be a very powerful tool. I also have a few relationship tables that are actually views.
This would have worked well, except the view was so huge is was unusable in reports. I could not get the performance up and reports took 30-45 minutes to complete at times.

Write a separate module for each audit table

This was far more work, but it solved the performance issue.
I decided just to expose the few modules that managers had asked for reports on in the past to cut down on the amount of code I needed to add.

Translating the Data - This is important too

So now I am going with option 2 the last thing I have to figure out is how I am going to translate the data in the before_value_string and after_value_string back into English. Those fields might contain an ID for one record and an option value for another. My first idea was to render them as ENUMs and then add a translation array to the language files. This worked great except there were 750,000 items in the array and it slowed everything down a great deal again. In addition to that, it is possible for two option lists to share a key so those values would not be correct. So I threw away that idea.

Then I realized that the before_value_text and after_value_text fields in the audit table were only used if the field being audited for that record was a TEXT field. So by definition it was already translated. So if I translated the other values in the "string" fields and put the translations in the "text" fields, I could report on the "text" fields and be assured of getting readable results. This also salved the duplicate key problem in option lists as each row is translated seperatly.

The Audit module wouldn't even blink an eye at any of this as it would ignore the "text" fields even if there were values in them.

So I worked out a scheduled job to translate records at night and a quick bit of code that would play catch up as each report is run. This way all records will always be translated when a report is run.

Building the Modules

So below I will outline how I created my report and some of the pitfalls I found along the way. I decided to start with Accounts. So I went into ModuleBuilder and built a module called Audit_Accounts and another module called Audit_Events to expose the audit_events table that was added for the EventRepository class. The way you would do this is to create a new project with a KEY set t 'Audit'. Then the name of the first module would be 'Accounts' and ModuleBuilder will make that 'Audit_Accounts' when you deploy it. If you want this functionality on more than just the Accounts module then create all Audit_* modules you need at this time and then follow the instructions below for each one.

Both are simple modules with just the basics and no relationships You also want to make sure these modules do NOT have tabs or teams. Once you have both modules set up, deploy them.

Once I was done with that I edited the modules starting with the Audit_Accounts module.

First we start with the modules/Audit_Accounts/vardefs.php file

Audit_Accounts Module

modules/Audit_Accounts/vardefs.php

	<?php
	//Define the parent module here, We need both plural and singular (biggest
	// mistake SugarCRM ever made) because of Opportunities mostly. By changing these two
	// values we can use this same vardefs for any module with an audit table
	$module = 'Accounts';
	$moduleSingular = 'Account';

	$dictionary['Audit_' . $module] = array(
	'table' => strtolower($module) . '_audit',
	'audited' => false,
	'activity_enabled' => false,
	'duplicate_merge' => false,
	'fields' => array(
	'id' => array(
	'name' => 'id',
	'type' => 'id',
	'reportable' => false,
	),
	'date_created' => array(
	'name' => 'date_created',
	'vname' => 'LBL_DATE_ENTERED',
	'type' => 'datetime',
	),
	//These two fields define the relationship between the audit table
	// and the parent module.
	'parent_id' => array(
	'name' => 'parent_id',
	'type' => 'id',
	'reportable' => false, //We don't need to report on the ID
	'required' => true,
	),
	'parent_name' =>
	array(
	'name' => 'parent_name',
	'type' => 'link',
	'relationship' => 'audits_' . $module . '_parent',
	'vname' => 'LBL_PARENT_NAME',
	'link_type' => 'one',
	'module' => $module,
	'bean_name' => $moduleSingular,
	'source' => 'non-db',
	),
	//These two fields define the relationship between the audit table
	// and the user that is being audited.
	'created_by' => array(
	'name' => 'created_by',
	'vname' => 'LBL_CREATED',
	'reportable' => false, //We don't need to report on the ID
	'type' => 'id',
	),
	'created_by_link' =>
	array(
	'name' => 'created_by_link',
	'type' => 'link',
	'relationship' => 'Audit_' . $module . '_created_by',
	'vname' => 'LBL_CREATED_BY_USER',
	'link_type' => 'one',
	'module' => 'Users',
	'bean_name' => 'User',
	'source' => 'non-db',
	),
	//These two fields define the relationship between the audit table
	// and the event that created the audit trail.
	'event_id' => array(
	'name' => 'event_id',
	'vname' => 'LBL_EVENT_ID',
	'type' => 'id',
	'reportable' => false, //We don't need to report on the ID
	),
	'event_id_link' =>
	array(
	'name' => 'event_id_link',
	'type' => 'link',
	'relationship' => 'Audit_' . $module . '_event',
	'vname' => 'LBL_EVENT_TEXT',
	'link_type' => 'one',
	'module' => 'Audit_Events', //This ties to our new module
	'bean_name' => 'Audit_Events',
	'source' => 'non-db',
	),
	//For this field I made it an ENUM and I have code that created the
	// language file for it once a day
	'field_name' => array(
	'name' => 'field_name',
	'vname' => 'LBL_FIELD_NAME',
	'type' => 'enum',
	'len' => 100,
	'options' => $module . '_field_name_list',
	'required' => true,
	),
	'data_type' => array(
	'name' => 'data_type',
	'type' => 'varchar',
	'len' => 100,
	'vname' => 'LBL_DATA_TYPE',
	'required' => true,
	),
	//These two fields will always contain the translated (English in my case) data
	// from the before_value_string or after_value_string fields. If the fieldtype is
	// TEXT then this field will already contain the TEXT that was entered in the field and
	// thus needs no translation
	'before_value_text' => array(
	'name' => 'before_value_text',
	'type' => 'text',
	'vname' => 'LBL_BEFORE_TEXT',
	),
	'after_value_text' => array(
	'name' => 'after_value_text',
	'vname' => 'LBL_AFTER_TEXT',
	'type' => 'text',
	),
	//These two fields will always contain the Database keys for the
	// data audited
	'before_value_string' => array(
	'name' => 'before_value_string',
	'type' => 'varchar',
	'len' => 255,
	'vname' => 'LBL_BEFORE_VALUE',
	),
	'after_value_string' => array(
	'name' => 'after_value_string',
	'vname' => 'LBL_AFTER_VALUE',
	'type' => 'varchar',
	'len' => 255,
	),
	//Every module requires a delete field or reports will fail with a SQL error
	'deleted' => array(
	'name' => 'deleted',
	'vname' => 'LBL_DELETED',
	'type' => 'bool',
	'required' => true,
	'default' => '0',
	'reportable' => false, //We don't need to report on the Deleted flag
	),
	),
	'indices' => array(
	array('name' => 'pk', 'type' => 'primary', 'fields' => array('id')),
	array('name' => 'parent_id', 'type' => 'index', 'fields' => array('parent_id')),
	array('name' => 'event_id', 'type' => 'index', 'fields' => array('event_id')),
	array('name' => 'pa_ev_id', 'type' => 'index', 'fields' => array('parent_id', 'event_id')),
	array('name' => 'after_value', 'type' => 'index', 'fields' => array('after_value_string')),
	),

	//Every audit table has 3 relationships.
	'relationships' => array(
	'Audit_' . $module . '_created_by' => array(
	'lhs_module' => 'Users',
	'lhs_table' => 'users',
	'lhs_key' => 'id',
	'rhs_module' => 'Audit_' . $module,
	'rhs_table' => strtolower($module) . '_audit',
	'rhs_key' => 'created_by',
	'relationship_type' => 'one-to-many'
	),
	'Audit_' . $module . '_event' => array(
	'lhs_module' => 'Audit_Events',
	'lhs_table' => 'audit_events',
	'lhs_key' => 'id',
	'rhs_module' => 'Audit_' . $module,
	'rhs_table' => strtolower($module) . '_audit',
	'rhs_key' => 'event_id',
	'relationship_type' => 'one-to-many'
	),
	'audits_' . $module . '_parent' => array(
	'lhs_module' => $module,
	'lhs_table' => strtolower($module),
	'lhs_key' => 'id',
	'rhs_module' => 'Audit_' . $module,
	'rhs_table' => strtolower($module) . '_audit',
	'rhs_key' => 'parent_id',
	'relationship_type' => 'one-to-many'
	),
	),
	//We don't need any of this stuff
	'optimistic_locking' => false,
	'unified_search' => false,
	'full_text_search' => false,
	);

view raw vardefs.php hosted with ❤ by GitHub

I added a lot of comments to that file. Notice the relationships near the middle. Several fields are marked as non-reportable as well. Read over the notes in that file and familiarize yourself with all the parts. When you are ready you can copy this file right over the current modules/Audit_Accounts/vardefs.php but DO NOT do a QR&R yet. For the two class files I didn't really see the need to add variables as the code is pretty simple. You would copy this over your modules/Audit_Accounts/Audit_Accounts_sugar.php

modules/Audit_Accounts/Audit_Accounts_sugar.php

	<?php
	class Audit_Accounts_sugar extends Basic {
	public $new_schema = true;
	public $module_dir = 'Audit_Accounts';
	public $object_name = 'Audit_Accounts';
	public $table_name = 'accounts_audit';
	public $importable = false;
	public $id;
	public $name;
	public $date_entered;
	public $created_by;
	public $created_by_name;
	public $deleted;
	public $created_by_link;
	public $field_name;
	public $data_type;
	public $before_value_text;
	public $after_value_text;
	public $before_value_string;
	public $after_value_string;
	public $event_id;
	public $event_id_link;
	public $disable_row_level_security = true;
	public $disable_custom_fields = true;

	public function bean_implements($interface){
	switch($interface){
	case 'ACL': return true;
	}
	return false;
	}

	}

view raw Audit_Accounts_sugar.php hosted with ❤ by GitHub

and then the modules/Audit_Accounts/Audit_Accounts.php file, again just copy this code over the current file.

modules/Audit_Accounts/Audit_Accounts.php

	<?PHP
	require_once('modules/Audit_Accounts/Audit_Accounts_sugar.php');

	class Audit_Accounts extends Audit_Accounts_sugar
	{
	static $already_ran = false;
	//Set this to the name of the parent module
	public $module = 'Accounts';

	public function __construct()
	{
	parent::__construct();
	// - When you run a report it instantiates this bean several times, we only need
	// to run this code once.
	// - The action 'BuildReportModuleTree' only happens when you run a report
	// so this will keep this code from running UNLESS you are actually
	// running a report
	if ((isset(self::$already_ran) && self::$already_ran == true) \|\|
	$_REQUEST['action'] != 'BuildReportModuleTree') {
	return;
	}
	self::$already_ran = true;
	//This code updates the language file and updates any records in the audit
	// table that do not have translated values in the 'text' fields.
	$ar = new auditReports($this->module);
	$ar->buildAuditSupport();
	}
	}

view raw Audit_Accounts.php hosted with ❤ by GitHub

And finally the language file. I delete all the other language files leaving only modules/Audit_Accounts/language/en_us.lang.php but that's up to you. In this case, just for the language file we have the translated name for Accounts. We call them Partners, you can change that to whatever you call your Accounts.

modules/Audit_Accounts/language/en_us.lang.php

	<?php
	/*
	* Your installation or use of this SugarCRM file is subject to the applicable
	* terms available at
	* http://support.sugarcrm.com/Resources/Master_Subscription_Agreements/.
	* If you do not agree to all of the applicable terms or do not have the
	* authority to bind the entity as an authorized representative, then do not
	* install or use this SugarCRM file.
	*
	* Copyright (C) SugarCRM Inc. All rights reserved.
	*/
	$moduleName = 'Partner';
	$mod_strings = array (
	'LBL_ASSIGNED_TO_ID' => 'Assigned User Id',
	'LBL_ASSIGNED_TO_NAME' => 'Assigned to',
	'LBL_TAGS_LINK' => 'Tags',
	'LBL_TAGS' => 'Tags',
	'LBL_ID' => 'ID',
	'LBL_DATE_ENTERED' => 'Date Created',
	'LBL_DATE_MODIFIED' => 'Date Modified',
	'LBL_MODIFIED' => 'Modified By',
	'LBL_MODIFIED_ID' => 'Modified By Id',
	'LBL_MODIFIED_NAME' => 'Modified By Name',
	'LBL_CREATED' => 'Created By',
	'LBL_CREATED_ID' => 'Created By Id',
	'LBL_DOC_OWNER' => 'Document Owner',
	'LBL_USER_FAVORITES' => 'Users Who Favorite',
	'LBL_DESCRIPTION' => 'Description',
	'LBL_DELETED' => 'Deleted',
	'LBL_NAME' => 'Name',
	'LBL_CREATED_USER' => 'Created by User',
	'LBL_CREATED_BY_USER' => 'Created by User',
	'LBL_MODIFIED_USER' => 'Modified by User',
	'LBL_LIST_NAME' => 'Name',
	'LBL_EDIT_BUTTON' => 'Edit',
	'LBL_REMOVE' => 'Remove',
	'LBL_EXPORT_MODIFIED_BY_NAME' => 'Modified By Name',
	'LBL_TEAM' => 'Teams',
	'LBL_TEAMS' => 'Teams',
	'LBL_TEAM_ID' => 'Team Id',
	'LBL_LIST_FORM_TITLE' => $moduleName . 's List',
	'LBL_MODULE_NAME' => $moduleName . 's Audit Table',
	'LBL_MODULE_TITLE' => $moduleName . 's Audit Table',
	'LBL_MODULE_NAME_SINGULAR' => $moduleName . ' Audit Table',
	'LBL_HOMEPAGE_TITLE' => 'My '.$moduleName . ' Audit Table',
	'LNK_NEW_RECORD' => 'Create '.$moduleName . ' Audit Table',
	'LNK_LIST' => 'View '.$moduleName . 's Audit Table',
	'LNK_IMPORT_AUDIT_ACCOUNTS' => 'Import '.$moduleName . 's',
	'LBL_SEARCH_FORM_TITLE' => 'Search '.$moduleName . ' Audit Records',
	'LBL_HISTORY_SUBPANEL_TITLE' => 'View History',
	'LBL_ACTIVITIES_SUBPANEL_TITLE' => 'Activity Stream',
	'LBL_AUDIT_ACCOUNTS_SUBPANEL_TITLE' => $moduleName . 's',
	'LBL_NEW_FORM_TITLE' => 'New '.$moduleName,
	'LBL_IMPORT' => 'Import '.$moduleName . 's',
	'LBL_EVENT_ID' => 'Event ID',
	'LBL_AFTER_VALUE' => 'Database String (after)',
	'LBL_BEFORE_VALUE' => 'Database String (before)',
	'LBL_AFTER_TEXT' => 'After Value',
	'LBL_BEFORE_TEXT' => 'Before Value',
	'LBL_FIELD_NAME' => 'Field Name',
	'LBL_DATA_TYPE' => 'Data Type',
	'LBL_PARENT_NAME' => $moduleName,
	'LBL_EVENT_TEXT' => 'Event Text'
	);

view raw en_us.lang.php hosted with ❤ by GitHub

After you get that done you could go though and clean up the client files but its not really necessary as there will be no way to get back to these modules since they don't have tabs and will not have links from reports.

I also delete the metadata/studio.pho file so that these modules do not show up in studio.

You MIGHT have to edit your custom/Extension/application/Ext/Include/Audit.php file to allow your new modules to both have no tabs and be available in reports. You file MIGHT be named differently depending on what you named your module in ModuleBuilder. This is what mine looks like.

custom/Extension/application/Ext/Include/Audit.php

	<?php
	//WARNING: The contents of this file are auto-generated
	$beanList['Audit_Accounts'] = 'Audit_Accounts';
	$beanFiles['Audit_Accounts'] = 'modules/Audit_Accounts/Audit_Accounts.php';
	$moduleList[] = 'Audit_Accounts';
	$modules_exempt_from_availability_check['Audit_Accounts'] = 'Audit_Accounts';
	$report_include_modules['Audit_Accounts'] = 'Audit_Accounts';
	$modInvisList[] = 'Audit_Accounts';

	$beanList['Audit_Events'] = 'Audit_Events';
	$beanFiles['Audit_Events'] = 'modules/Audit_Events/Audit_Events.php';
	$moduleList[] = 'Audit_Events';
	$modules_exempt_from_availability_check['Audit_Events'] = 'Audit_Events';
	$report_include_modules['Audit_Events'] = 'Audit_Events';
	$modInvisList[] = 'Audit_Events';
	?>

view raw Audit.php hosted with ❤ by GitHub

<?php

//WARNING: The contents of this file are auto-generated

$beanList['Audit_Accounts'] = 'Audit_Accounts';

$beanFiles['Audit_Accounts'] = 'modules/Audit_Accounts/Audit_Accounts.php';

$moduleList[] = 'Audit_Accounts';

$modules_exempt_from_availability_check['Audit_Accounts'] = 'Audit_Accounts';

$report_include_modules['Audit_Accounts'] = 'Audit_Accounts';

$modInvisList[] = 'Audit_Accounts';

$beanList['Audit_Events'] = 'Audit_Events';

$beanFiles['Audit_Events'] = 'modules/Audit_Events/Audit_Events.php';

$moduleList[] = 'Audit_Events';

$modules_exempt_from_availability_check['Audit_Events'] = 'Audit_Events';

$report_include_modules['Audit_Events'] = 'Audit_Events';

$modInvisList[] = 'Audit_Events';

?>

view raw Audit.php hosted with ❤ by GitHub

Audit_Events Module

This module is very simple. It really only has one job, to expose the audit_events table to the reports module.

Scheduled Job

So in addition to the modules above we need a way to translate the raw data in the audit table into readable data. I created a utility that could be run at night to mass update all the needed modules and also be run at "report" time to catch up any records that were recently added.

One issue is that if your system is anywhere as big as mine you might have millions of records in your audit logs. On my system, running the scheduled job every 10 minutes it too about 5 days to update all the data in 10 modules. So you will have to manage expectations until your data is all caught up.

I placed this at custom/Extension/application/Ext/Utils/audit.php and you can just copy this file into that location

custom/Extension/application/Ext/Utils/audit.php

	<?php

	class auditReports
	{
	private $parent;
	private $module;
	private $processed = array();
	private $tableArray = array();

	/**
	* auditReports constructor.
	* @param string $module - the bean name of the parent module
	*/
	public function __construct($module)
	{
	$bean = BeanFactory::newBean($module);
	$this->parent = $bean;
	$this->module = $module;
	}

	/**
	* Refresh all Audit Report related information
	* @param int $recordsToProcess
	*/
	public function buildAuditSupport($recordsToProcess = -1)
	{
	$newLangList = array();
	//I arbitrarily check the date on the english language file, you can check any you want.
	$fileTime = filemtime("custom/Extension/application/Ext/Language/en_us.sugar_{$this->module}_field_name_list.php");
	if ($fileTime !== false) {
	$timeSinceUpdate = time() - $fileTime;
	} else {
	$timeSinceUpdate = time();
	}
	//Crete a language file with all the translated names for all the fields contained in this module
	//Just do this once a day, you can adjust as needed
	if (!empty($this->module) && $timeSinceUpdate > 86400) {
	foreach ($this->parent->field_defs as $fieldName => $fieldDefinition) {
	if (isset($this->parent->field_defs[$fieldName]['vname']) && !empty($this->parent->field_defs[$fieldName]['vname'])) {
	$newLangList[$fieldName] = translate($this->parent->field_defs[$fieldName]['vname'],
	$this->module);
	}
	}
	$dropDownList = $this->module . '_field_name_list';
	$this->addItemsToDropdown($dropDownList, $newLangList);
	$GLOBALS['log']->fatal("Audit: Creation of the {$dropDownList} option list is complete.");
	}

	$table = strtolower($this->module);
	//We want to remove any records that hold no data what so ever.
	$deleteSQL = "DELETE FROM {$table}_audit
	WHERE COALESCE(before_value_text,'')='' AND COALESCE(after_value_text,'')='' AND
	COALESCE(before_value_string,'')='' AND COALESCE(after_value_string,'')=''";
	$result = $GLOBALS['db']->query($deleteSQL, true);
	if ($result) {
	$numOfRows = $GLOBALS['db']->getAffectedRowCount($result);
	} else {
	$numOfRows = 0;
	}
	if ($numOfRows > 0) {
	$GLOBALS['log']->fatal("Audit: Deleted {$numOfRows} empty rows from the audit table {$table}_audit.");
	}
	//Now we want to go to the audit table and collect up all the records that have not been translated as yet. We
	// do this by getting all the records with a NULL in BOTH before_value_text and after_value_text
	$usedIDs = "SELECT a.id,a.field_name,a.data_type,a.before_value_string,a.after_value_string
	FROM {$table}_audit a
	WHERE COALESCE(before_value_text,'')='' AND COALESCE(after_value_text,'')=''
	ORDER BY date_created DESC";
	if ($recordsToProcess > 0) {
	$usedIDs .= " LIMIT {$recordsToProcess}";
	}
	$result = $GLOBALS['db']->query($usedIDs, true);
	if ($result) {
	$numOfRows = $GLOBALS['db']->getRowCount($result);
	} else {
	$numOfRows = 0;
	}
	//We don't cache these for a few reasons. Mainly because of quoting issues. I might work this out in
	// future versions.
	$noCache = array('name', 'phone', 'varchar');
	$GLOBALS['log']->fatal("Audit: Processing {$numOfRows} rows from {$table}_audit");
	$processCount = 0;

	while ($hash = $GLOBALS['db']->fetchByAssoc($result)) {
	//Every 500 rows check how many are left
	if ($processCount++ % 1000 == 0) {
	$remainingSQL = "SELECT count(a.id)
	FROM {$table}_audit a
	WHERE COALESCE(before_value_text,'')='' AND COALESCE(after_value_text,'')=''";
	$count = $GLOBALS['db']->getOne($remainingSQL);
	$GLOBALS['log']->fatal("Audit: Number of unprocessed rows remaining in {$table}_audit: {$count} [{$processCount}/{$numOfRows}]");
	}
	$fieldName = $hash['field_name'];
	$dataType = $hash['data_type'];
	$bvs = $hash['before_value_string'];
	$avs = $hash['after_value_string'];
	$id = $hash['id'];

	//Handle before_value_string
	$isProcessed = (array_key_exists('before' . $bvs . $fieldName, $this->processed) && !in_array($dataType,
	$noCache));
	if (!$isProcessed && !empty($bvs)) {
	if (!in_array($dataType, $noCache)) {
	$this->processed['before' . $bvs . $fieldName] = 1;
	}
	$this->updateAuditTable($dataType, $table, $bvs, $fieldName, $id, 'before');
	}

	//Handle after_value_string
	$isProcessed = (array_key_exists('after' . $avs . $fieldName, $this->processed) && !in_array($dataType,
	$noCache));
	if (!$isProcessed && !empty($avs)) {
	if (!in_array($dataType, $noCache)) {
	$this->processed['after' . $avs . $fieldName] = 1;
	}
	$this->updateAuditTable($dataType, $table, $avs, $fieldName, $id, 'after');
	}
	}
	if($numOfRows==0) {
	$GLOBALS['log']->fatal("Audit: Audit Rebuild for the {$table}_audit is complete.");
	}
	}

	/**
	* @param $dropDownList
	* @param $item_list
	*/
	private function addItemsToDropdown($dropDownList, $item_list)
	{
	require_once('modules/ModuleBuilder/MB/ModuleBuilder.php');
	require_once('modules/ModuleBuilder/parsers/parser.dropdown.php');
	$parser = new ParserDropDown();
	$params = array();
	$_REQUEST['view_package'] = 'studio'; //need this in parser.dropdown.php
	$params['view_package'] = 'studio';
	$params['dropdown_name'] = $dropDownList; //replace with the dropdown name
	$params['dropdown_lang'] = 'en_us';//create your list...substitute with db query as needed
	foreach ($item_list as $k => $v) { //merge new and old values
	$drop_list[] = array($k, $v);
	}
	//TODO:Update this to use namespaces
	$json = getJSONobj();
	$params['list_value'] = $json->encode($drop_list);
	$parser->saveDropDown($params);
	}

	/**
	* This is my version 0.1 of code to figure out what module a 'relate' ID is relating to. Its not very good but
	* it works reliably. I want to make it a but more fields_defs aware in the next version and remove some of this
	* hard coded logic
	*
	* @param string $fieldName
	* @param string $value
	* @return string
	*/
	private function getRelateValue($fieldName, $value)
	{
	$fieldDefs = $this->parent->field_defs[$fieldName];
	if (isset($fieldDefs['type']) && ($fieldDefs['type'] == 'relate' \|\| $fieldDefs['type'] == 'email')) {
	$module = $fieldDefs['module'];
	} elseif (isset($this->parent->field_defs[$fieldDefs['group']]['module'])) {
	$module = $this->parent->field_defs[$fieldDefs['group']]['module'];
	} elseif (stristr($fieldDefs['name'], 'user') !== false) {
	$module = 'Users';
	} elseif (stristr($fieldDefs['name'], 'team') !== false) {
	$module = 'Teams';
	} elseif ($fieldName == 'parent_id') {
	$fieldDefs = $this->parent->field_defs['parent_name'];
	$module = $fieldDefs['module'];
	} else {
	//if there is a field in the audit table I can't translate I put this in the log to alert me
	$GLOBALS['log']->fatal("Unhandled fieldName in {$this->parent->module} Audit trail: {$fieldName}");
	$module = null;
	}

	if (!array_key_exists($module, $this->tableArray) && $module != null) {
	$relBean = BeanFactory::getBean($module);
	if (isset($relBean->field_defs['first_name'])) {
	$mainName = "CONCAT(first_name,' ',last_name)";
	} elseif (isset($relBean->field_defs['email_address'])) {
	$mainName = 'email_address';
	} else {
	$mainName = 'name';
	}
	$modInfo = $relBean->table_name . '/' . $mainName;
	$this->tableArray[$module] = $modInfo;
	} else {
	$modInfo = $this->tableArray[$module];
	}
	list($table_name, $name) = explode('/', $modInfo, 2);
	$dataSQL = "SELECT {$name} FROM {$table_name} WHERE id='{$value}'";
	$nameOfRecord = $GLOBALS['db']->getOne($dataSQL);
	//if the parent has been deleted from the system use this
	if (empty($nameOfRecord)) {
	$nameOfRecord = '---COULD NOT LOCATE PARENT RECORD---';
	}
	return $nameOfRecord;
	}

	/**
	* This function does the SQL to update the before_value_text and after_value_text values in the audit table
	*
	* @param string $dataType
	* @param string $table
	* @param string $currentValue
	* @param string $fieldName
	* @param string $id
	* @param string $auditFieldPrefix
	*/
	private function updateAuditTable($dataType, $table, $currentValue, $fieldName, $id, $auditFieldPrefix)
	{
	global $app_list_strings;
	switch ($dataType) {
	case 'name':
	case 'phone':
	case 'varchar':
	$currentValue = $GLOBALS['db']->quote(htmlspecialchars_decode(str_replace('\\', '', $currentValue),
	ENT_QUOTES));
	if (stristr($currentValue, "'") !== false) {
	$updateSQL = "UPDATE {$table}_audit SET {$auditFieldPrefix}_value_text='{$currentValue}' WHERE id='{$id}'";
	} else {
	$updateSQL = "UPDATE {$table}_audit SET {$auditFieldPrefix}_value_text='{$currentValue}' WHERE {$auditFieldPrefix}_value_string='{$currentValue}'";
	$this->processed[$auditFieldPrefix . $currentValue . $fieldName] = 1;
	}
	$GLOBALS['db']->query($updateSQL, true);
	break;
	case 'int':
	case 'date':
	case 'decimal':
	//These values I update ALL records that have the same text in the string field. This means I only and
	// to use one SQL update to add translated values to potentially tens or hundreds of records.
	$currentValue = $GLOBALS['db']->quote(htmlspecialchars_decode(str_replace('\\', '', $currentValue),
	ENT_QUOTES));
	$updateSQL = "UPDATE {$table}_audit SET {$auditFieldPrefix}_value_text='{$currentValue}' WHERE {$auditFieldPrefix}_value_string='{$currentValue}'";
	$GLOBALS['db']->query($updateSQL, true);
	break;
	case 'currency':
	//I mass update these fields too but I include a hard coded $ as I couldn't get the SugarCRM currency
	// code to work for some reason. I will fix this in a future version
	if (empty($currentValue)) {
	$newValue = '0.00';
	} else {
	$newValue = number_format($currentValue, 2, '.', ',');
	}
	//TODO: use SugarCRM currency functions
	$updateSQL = "UPDATE {$table}_audit SET {$auditFieldPrefix}_value_text='\${$newValue}' WHERE {$auditFieldPrefix}_value_string='{$currentValue}'";
	$GLOBALS['db']->query($updateSQL, true);
	break;
	case 'datetimecombo':
	case 'datetime':
	//In this one, we format the date to the Admins date format and timezone. We cant do it to the report user as we store this
	//perfectly in the DB
	$datetime = new datetime($currentValue, new DateTimeZone('UTC'));
	$timedate = new TimeDate();
	$formattedDate = $timedate->asUser($datetime, BeanFactory::getBean('Users', '1'));
	$updateSQL = "UPDATE {$table}_audit SET {$auditFieldPrefix}_value_text='{$formattedDate} EDT' WHERE {$auditFieldPrefix}_value_string='{$currentValue}'";
	$GLOBALS['db']->query($updateSQL, true);
	break;
	case 'relate':
	case 'id':
	case 'email':
	case 'team_list':
	if ($dataType = 'email' && stristr($currentValue, '@') !== false) {
	$updateSQL = "UPDATE {$table}_audit SET {$auditFieldPrefix}_value_text='{$currentValue}' WHERE {$auditFieldPrefix}_value_string='{$currentValue}'";
	} else {
	//Get the name of the related record
	$nameOfRecord = $this->getRelateValue($fieldName, $currentValue);
	$updateSQL = "UPDATE {$table}_audit SET {$auditFieldPrefix}_value_text='{$nameOfRecord}' WHERE {$auditFieldPrefix}_value_string='{$currentValue}'";
	}
	$GLOBALS['db']->query($updateSQL, true);
	break;
	case 'bool':
	//Checkboxes
	if ($currentValue == 1) {
	$checked = 'Checked';
	} else {
	$checked = 'Unchecked';
	}
	$updateSQL = "UPDATE {$table}_audit SET {$auditFieldPrefix}_value_text='{$checked}' WHERE {$auditFieldPrefix}_value_string='{$currentValue}' AND
	data_type='{$dataType}'";
	$GLOBALS['db']->query($updateSQL, true);
	break;
	case 'enum':
	case 'multienum':
	//Get a translated Dropdown value
	$optionList = $this->parent->field_defs[$fieldName]['options'];
	$optionArray = $app_list_strings[$optionList];
	if ($dataType == 'enum') {
	$nameOfOption = $optionArray[$currentValue];
	if (empty($nameOfOption)) {
	$nameOfOption = $currentValue . ' [UNDEFINED]';
	}
	} else {
	$listOfNames = array();
	$list = explode('^,^', substr($currentValue, 1, -1));
	foreach ($list as $item) {
	$nameOfOption = $optionArray[$item];
	if (empty($nameOfOption)) {
	$nameOfOption = $currentValue . ' [UNDEFINED]';
	}
	$listOfNames[] = $nameOfOption;
	}
	$nameOfOption = implode(', ', $listOfNames);
	}
	$updateSQL = "UPDATE {$table}_audit SET {$auditFieldPrefix}_value_text='{$nameOfOption}' WHERE {$auditFieldPrefix}_value_string='{$currentValue}' AND
	field_name='{$fieldName}'";
	$GLOBALS['db']->query($updateSQL, true);
	break;
	default:
	//If we have a new or unaccounted for dataTyp just log it to the sugarcrm.log file
	$GLOBALS['log']->fatal("Unhandled DataType in {$this->parent->module} Audit trail: {$dataType}");
	break;
	}
	}
	}

view raw audit.php hosted with ❤ by GitHub

So with that in place you can add a scheduler you can set to at night or whatever you want. Just add this file. You can see that I tell it to only process 1000 records from each module at a time. I found that allowing any more just clogged the whole system up.

custom/Extension/modules/Schedulers/Ext/ScheduledTasks/auditReports.php

	<?php
	if (!defined('sugarEntry') \|\| !sugarEntry) {
	die('Not A Valid Entry Point');
	}

	array_push($job_strings, 'auditReports');

	function auditReports()
	{
	//Just add all the modules you have added to this list by PARENT MODULE
	$auditedModules = array('Accounts', 'Bugs', 'Cases', 'Contacts', 'IN_Customer_Contacts', 'IN_Customers',
	'IN_Orders', 'Opportunities', 'Quotes', 'Tasks');
	$GLOBALS['log']->fatal('----->Scheduler fired job of type auditReports()');
	foreach ($auditedModules as $auditedModule) {
	$ar = new auditReports($auditedModule);
	$ar->buildAuditSupport(1000);
	}
	$GLOBALS['log']->fatal('----->Scheduler finished job of type auditReports()');
	return true;
	}

view raw auditReports.php hosted with ❤ by GitHub

And its language file...

custom/Extension/modules/Schedulers/Ext/Language/en_us.auditReports.php

	<?php

	$mod_strings['LBL_AUDITREPORTS'] = 'Audit Report Rebuild';

view raw en_us.auditReports.php hosted with ❤ by GitHub

Reporting on the audit_events table

Now the last thing we have to make work is the JSON data in the audit_events table. There is no SugarWidget to allow the reports module to render JSON data so we have to make one. The displayListPlain function at the end is where all the magic happens, Normally you just include the 'source' field in your report and I make it so that just the single word 'LogicHook' or 'User' shows up just as in the built in Audit viewer. But in the report, if you click on that word, it opens up and shows you the whole array. It just made the reports looks cleaner.

custom/include/generic/SugarWidgets/SugarWidgetFieldjson.php

	<?php
	class SugarWidgetFieldjson extends SugarWidgetReportField
	{
	public function queryFilterEquals($layout_def)
	{
	return $this->_get_column_select($layout_def) . "='" . $GLOBALS['db']->quote($layout_def['input_name0']) . "'\n";
	}

	public function queryFilterNot_Equals_Str($layout_def)
	{
	$field_name = $this->_get_column_select($layout_def);
	$input_name0 = $GLOBALS['db']->quote($layout_def['input_name0']);
	return "{$field_name} != '{$input_name0}' OR ({$field_name} IS NULL)\n";
	}

	public function queryFilterContains(&$layout_def)
	{
	return $this->_get_column_select($layout_def) . " LIKE '%" . $GLOBALS['db']->quote($layout_def['input_name0']) . "%'\n";
	}

	public function queryFilterdoes_not_contain(&$layout_def)
	{
	$field_name = $this->_get_column_select($layout_def);
	$input_name0 = $GLOBALS['db']->quote($layout_def['input_name0']);
	return "{$field_name} NOT LIKE '%{$input_name0}%' OR ({$field_name} IS NULL)\n";
	}

	public function queryFilterStarts_With(&$layout_def)
	{
	return $this->_get_column_select($layout_def) . " LIKE '" . $GLOBALS['db']->quote($layout_def['input_name0']) . "%'\n";
	}

	public function queryFilterEnds_With(&$layout_def)
	{
	return $this->_get_column_select($layout_def) . " LIKE '%" . $GLOBALS['db']->quote($layout_def['input_name0']) . "'\n";
	}

	public function queryFilterone_of($layout_def)
	{
	foreach ($layout_def['input_name0'] as $key => $value) {
	$layout_def['input_name0'][$key] = $GLOBALS['db']->quote($value);
	}
	return $this->_get_column_select($layout_def) . " IN ('" . implode("','", $layout_def['input_name0']) . "')\n";
	}

	public function displayInput($layout_def)
	{
	$str = '<input type="text" size="20" value="' . $layout_def['input_name0'] . '" name="' . $layout_def['name'] . '">';
	return $str;
	}

	/**
	* This is where the magic happens.
	* @param $layout_def
	* @return string
	*/
	public function displayListPlain($layout_def)
	{
	$value = $this->_get_list_value($layout_def);
	$displayValue = json_decode(htmlspecialchars_decode($value), true);
	if (isset($layout_def['widget_type']) && $layout_def['widget_type'] == 'checkbox') {
	if ($value != '' && ($value == 'on' \|\| intval($value) == 1 \|\| $value == 'yes')) {
	return "<input name='checkbox_display' class='checkbox' type='checkbox' disabled='true' checked>";
	}
	return "<input name='checkbox_display' class='checkbox' type='checkbox' disabled='true'>";
	}
	if($layout_def['name']=='source') {
	$divID = $layout_def['name'] . '_' . $layout_def['fields']['PRIMARYID'];
	return "<div onclick=\"document.getElementById('{$divID}').style.display='block';\">{$displayValue['subject']['_type']}</div><div id='{$divID}' style='display: none;overflow: hidden;'><pre>" . print_r($displayValue,
	true) . '</pre></div>';
	} else {
	return '<pre>'.print_r($displayValue, true).'</pre>';
	}
	}
	}

view raw SugarWidgetFieldjson.php hosted with ❤ by GitHub

Conclusion

After all of this I have a usable module I can do reports on but does not interfere with the stock Audit code. I can filter by user or field name or date range. All of the things my users request.

Now if you want to create the functionality on more than one module just create all the needed Audit_* modules up front and then do the same editing to them all.

Files are available here on GitHUB and you can download them or if need any help at all making this work just email me at kenbrill@gmail.com and I will get you what you need. If you like this or hate this PLEASE let me know. If you have any ideas to make it better or faster PLEASE forward them to me as well.

Code from the creek...

Wednesday, March 27, 2019

Adding Audit Logs to Reports