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

1. I need a way to report on any modules audit log
2. I need a way that allows a manager to create their own reports
3. That method has to be familiar to them already (this was really me saying I wanted it in the Reports module)
4. It must not allow editing of Audit Logs in any way
5. It must be fast over millions of records (some of our audit logs are huge)
6. It must translate all the raw data back into English
7. It must allow our system admins to include that new audit_events table data in the reports
8. 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

1. 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
   ....
1.  I like making modules with views.  It can be a very powerful tool.  I also have a few relationship tables that are actually views.
2. 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.
2. Write a separate module for each audit table
1. This was far more work, but it solved the performance issue.
2. 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

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

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

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

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

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

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

And its language file...

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

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

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.