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

Asynchronous Bean Saves

Why I needed this

In my SugarCRM installation I have many logic hooks that update values in other modules.  For example, when you save a note it, in the before_save logic hook, it updates a "Last Note Update" field (among others) in the related Case.  So that means that you save the note and it has to run all the logic hooks, workflows, advanced workflows and SugarLogic on that note and then it would have to run all that stuff on the related case as well, all while the user waits for their save to finish.

In the past to avoid the overhead of a bean save I would just update the related bean with a direct SQL call.  But this means you lose out on logic_hooks, workflow and all the rest.  So if you can off-load all that extra bean save overhead to a JobQueue job, it will speed up saves while not impacting workflow or logic hooks the way a direct SQL call would.  The JobQueue is explained in the developer guide here.

So this is an example of the kind of code I am talking about and the new code I use



The 'new' code would call a function I have placed in a file called custom/Extension/application/Ext/Utils/UpdateBean.php.  This makes it available anywhere in the app. Custom utilities are in the developer guide here.  That file looks like this

That function submits a job to the job queue and it will run the next time cron runs.  So you bean save might be delayed by a minute but your user doesn't have to sit through it.  The final file, the job itself is in a file called custom/Extension/modules/Schedulers/Ext/ScheduledTasks/updateBeanJob.php and looks like this



So thats it, easy Asynchronous bean saves.

A Custom SugarLogic Expression

I made my first custom SugarLogic expression tonight. It turned out to be fairly easy but the Manual has small error in it and it fails to explain one thing.

 So here is the complete story.  First, this is the Developer Guide Manual page for this. Its fairly complete but it tells you to run the SugarLogic repair from the 'Schedulers' menu instead of the 'Repair' menu, small detail. It also does not really go over where the little 'how to' popup text is created. I will go over that here.

 For this demo I made a simple SugarLogic expression that returns the number of years from a date until now. It was to show the age of a person based on a birthday field. Here is the code that I placed in this file 'custom/include/Expressions/Expression/Date/NumberOfYearsExpression.php'


Once you have the file in place you run the Admin > Repair > Rebuild Sugar Logic Functions repair and then clear your local browser cache. That last step is crucial.

There is a popup on the Expression Editor that explains hwo to use a function, in this case it looks like this

The text for this popup comes from that comment at the top of the file there, I dont know how or where they do that and it seems rather silly to do it that way but there you go.  The comment in question is above the CLASS in the code

/**
 * <b>NumberOfYears(Date d)</b><br>
 * Returns number of years since the specified date.
 */

Make a new team the primary team with confirmation alert

Where I work we use Tasks to pass around work from one department to the next.  So a Lead might come in and a Task on that Lead is assigned to the Sales team.  Once the Sales team finishes whatever they need to do they assign the Task to the Legal team.  The Legal team then passes it to the Order Management team and so on.

One of the issues with this was that we couldnt preadd the teams as tasks take their own path sometimes and it muddied reports when we did that.  So each team needed to add the next team to the Task and mark it as Primary. 

Well, as with most manual workflows, it didn't always get marked as primary.  So I came up with the code below to ask the user, after they add a team, if they want to make it the primary team and if they say yes, then mark it as primary.  It took forever to figure out how to tell if a team had been added, and I dont really like this solution but it worked the best so I went with it.

So first the code, it just goes in the record.js of whatever module you want to add this to.



Then add a line to the language file custom/include/language/en_us.lang.php that reads.  Notice how I am filling in the 'TeamName' variable with the app.lang.get() code in the record.js file.



With this code in place (after a QR&R of course) when a new team is added, the system will ask the use if they want that team marked as primary.  If they say yes then it will be.

Undocumented Alert() options - Confirmation label buttons

Normally, on a confirmation alert the two options are Confirm and Cancel.  You can change the labels on these two buttons with the code below.

User names in the sugarcrm.log instead of IDs

So normally the sugarCRM log file is filled with lines like this

Mon Apr 16 07:02:12 2018 [15900][412b8e70-febc-11e6-934a-52d504b662cb][FATAL] No Links found for relationship tasks_activities_1_tasks
Mon Apr 16 07:02:12 2018 [15900][420703ed-f2bc-e3f4-f906-566ef6f1e16d][FATAL] No Links found for relationship accounts_accounts_1
Mon Apr 16 07:02:12 2018 [15900][41a5d4a2-c01b-11e7-88b5-52d504b662cb][FATAL] No Links found for relationship cases_cases_1

Usually I am looking for an entry from a particular user so GUIDs are hard for old human eyes to read.  Even with FIND its a pain.  So I wrote this


This writes out a script and, when run, will rewrite the sugarcrm.log file with user_names instead of IDs.

So to use this you need to

1. Copy this code to the root SugarCRM directory
2. run it (php -f updateLog.php)
3. Update the permissions on the file it creates (chmod 777 updateLog.sh)
4. Run the script (./updateLog.sh)

Of course this only works on Linux or Mac and maybe the Linux Shell in windows, not sure abou thtat.  But anyway I like it and I hope up do to.

This could also be done in real time by overriding include/SugarLoggger/SugarLogger.php and changing the bit of code that write the line to the log file.  I can show that if anyone needs it, just ask.

Are your custom fields out of control?

FieldManager 1.0 for MySQL

Every SugarCRM installation I have ever worked on has had the same problem.  Custom fields that are out of control.  There are several problems with Studio when it comes to custom fields.  It really doesnt tailor the field to the data it will contain.

1. The size option for VARCHARs defaults to 255, for dropdowns it's 100 even though we know the exact size of the data that will fill it.  
2. It doesnt allow for CHARS.  
3. Fields that are filled from formulas should have the option to be non-db fields as they are usuaally for display only.  
4. Too many people use TEXT when a VARCHAR would work fine.  
5. Fields are created and then never used or fields are abandoned in favor of something new.
6. Fields are created and then are used for a datatype they are not intended for like using a VARCHAR to hold a date or numeric.
7. Fields are added and then so rarely used it begs for a redesign.  Maybe there is a checkbox that has never been checked or a dropdown that everyone lets default.