Auditing logging is a common request from customers—the ability to track database activity including adding, editing, and deleting records, who changed it and when. In some types of businesses, like medical and financial, the law requires it. Generally, there are two approaches to audit logging in FileMaker, either native functionality or a third-party tool.
WorldSync has two products that provide audit log functionality. FMDataGuard is a client-side (FileMaker Pro) plug-in that can log database activity using function calls in various calculation windows in FileMaker Pro (security, validation, etc.). SyncDek is a separate server application that communicates with FileMaker Server Advanced via JDBC to log database activity.
While FMDataGuard works well in typical situations, there are some performance considerations in high transaction and network environments. The reason is all the effort to log database activity happens client-side and must travel over the network in a multi-user environment. SyncDek provides a solution to performance issues because it moves a significant amount of the effort to log database activity to the server.
Earlier this year we developed a new medical research database for one of the top cardiothoracic surgeons in the United States. Since the database contains “identifying” patient information, it must log database activity. We decided to implement FMDataGuard to meet these HIPAA requirements.
The database was deployed in a Citrix environment with FileMaker Server. During beta testing, the users reported slow performance that we determined was due to the overhead for FMDataGuard. Per the recommendation from Jason Erickson at WorldSync, we implemented SyncDek to replace FMDataGuard and the performance issues were resolved.
SyncDek is primarily known for database replication (syncing). However, it has additional functionality including backup recovery and audit logging. SyncDek offers a couple of different approaches to audit logging. The “PubFlag” method queries the status of the PubFlag field in each table to determine the activity to log. The advantage of the PubFlag method is that it is compatible with FileMaker Go (iPhone/iPad) and server-side scripting with FileMaker Server.
The “Live Posting” method uses the SyncDek plug-in (not the same as FMDataGuard) to push a request to log activity to the SyncDek server. The advantage of Live Posting is the auditing of activity can be near real-time. Live Posting also supports logging views, which the PubFlag method does not.
In our case we opted for the PubFlag method for logging adds, edits and deletes to move as much of the effort to the server as possible. Since logging views was also a requirement for this customer, we used Live Posting although the client-side impact to log views is light except for large found sets.
To gain a better understanding of what’s happening under the hood, I created a contact database with 5,000 records and implemented each method in turn. SyncDek uses a “temp” table in FileMaker to store requests to log database activity. It also stores the audit log in FileMaker (although it is could be a SQL table).
I opened the contact database from FileMaker Server and created three windows—one for the data entry layout, one for the temp table, and one for the audit log table. By creating, editing and deleting records in the data entry window, I was able to see SyncDek processing records in the temp table and creating records in the audit log table. If you implement SyncDek, I strongly recommend using this practice to see how SyncDek works.
To log deletes using the PubFlag method, WorldSync recommends using a custom menu to override the Delete Record and Delete All Records commands. The custom menu calls a script to change the value in the PubFlag field to “4″ to mark the record for deletion. SyncDek then performs the delete and stores it in the audit log.
To log views using the Live Posting method, the Sync_LogView function is added to the limited View privilege for each table under Security. If you have three windows open, you will see SyncDek post a record to the “temp” table for the view. Then during the next cycle, SyncDek will track the view in the audit log table and delete the record from the “temp” table. Of course, this requires the SyncDek_TCP plug-in to be installed with FileMaker Pro.
Once I had an understanding of how SyncDek works, I started experimenting with some alternate techniques. When the user performs a Find and SyncDek is logging views, it creates a View record in the “temp” table for every record in the found set. For founds sets of a few hundred records or more, this can have a negative impact on performance. My challenge was to determine a way to log a view for only the first record in the found set immediately after performing a Find.
My first idea was to use a custom menu, the same technique as logging deletes with the PubFlag method. This would override the default behavior of the Previous and Next commands in the Go To Record submenu. Unfortunately, the Previous and Next commands are not available in Custom Menus. Specifically, I wanted to use the “Based on existing command” option so logging would occur even if the user clicked the “Go to previous/next record” buttons in the FileMaker toolbar. (Request to FMI: Please provide the ability to override all commands and subcommands in the standard FileMaker menus via Custom Menus.)
For my second attempt, I tried to create a limited View calculation to check a condition and decide whether to call the Sync_LogView function. I tried several variations including setting a global variable via a script or conditional formatting, and using the Get ( WindowMode ) function. Unfortunately, the order of execution in the limited View calculation does not return a consistent result. If there were a way around this limitation, there are several possibilities to implement this technique.
For my third attempt, I setup an OnRecordLoad trigger to run a script with the Sync_LogView function. It works! The downside is the script trigger must be setup separately for each individual layout. Also, since it is implemented at the interface layer (layout) instead of the data layer (security), it may not meet the requirements in some regulatory situations.
On the upside, the script for the OnRecordLoad trigger, can be modified to work natively with FileMaker (without a plug-in). This replaces the Sync_LogView function by creating a record directly in the “temp” table. (I got this idea while watching activity in the temp and audit tables in the three-window scenario.)
Audit logging is one of three core features available with SyncDek. If you are already using the backup/recovery or syncing functionality, it is a no-brainer to use the audit logging, if you need it. If you are using FMDataGuard or another product/technique, SyncDek provides an alternative to address performance issues on the client-side.