Importance of Audit Logs

While many systems have a simple “last updated by” system to track who made the most recent change in a system, audit logs take this type of detail a step further. Audit logs record every occurrence of a change, what time it happened, what user or service performed the action, and the actual item impacted. These logs don’t get overwritten each time a new event occurs – they’re kept in chronological order. Keeping this type of history for your system can be vital for several reasons:

• Security: Audit logging provides easy monitoring that can help with detecting unauthorized access or suspicious activities.

• Data Integrity: Keeping a record of changes ensures that changes to records are legitimate and traceable.

• Compliance: Sometimes, audit logs are required for regulatory requirements for data management and audit trails.

• Troubleshooting: Having historical logs can be critical in identifying and resolving issues by reviewing past changes.

Considerations for Logging

When choosing how to create audit logs for your system in FileMaker, there are some important things to consider. First and foremost, knowing what you want to log is just as important as how you log it. You can ask yourself a few questions: is it necessary to log everything? Are there only key components to keep track of? These foundational questions can greatly shape what approach you take to your audit logging solution.

Other factors that might determine how you create your audit logs in FileMaker involve size and development effort. When taking size into consideration, it’s important to consider how much bloat you are willing to handle, as audit logs will take up a considerable amount of space over time. One way to address this is by asking how long you are going to keep the audit log records. For files that can't handle much bloat, consider things such as purge scripts that automatically delete records older than a certain date.

When it comes to development efforts, there's typically a high implementation cost to any option for audit logs. Auto-enter based logs are going to be the easiest to implement as they mostly use native FileMaker capabilities, but likely won’t provide as much data. On the other hand, a table-by-table approach, for example, is going to have a higher implementation cost as it encompasses a larger data pool.

Native FileMaker Capabilities

FileMaker provides native capabilities to create custom audit logs by leveraging JSON for a structured and flexible format. One method is using script triggers such as OnRecordCommit, which fires whenever a record is committed, or OnObjectSave, which fires after a chosen field is saved. Adding one of these script triggers to a housekeeping field, such as one that tracks a modification timestamp, would then be able to call your script that builds a JSON object for your audit log. Alternatively, you can leverage the OnWindowTransaction script trigger to call a logging script at the file level. This script trigger provides a method to log every create, update, and delete interaction.

OnObjectSave and OnRecordCommit

For smaller systems, it may be sufficient to use a script that is triggered through OnObjectSave or OnRecordCommit. This script would then make a new entry into a dedicated audit log table, recording the record’s ID as a foreign key, and storing the actual event information as a JSON object. One drawback to this approach to consider, however, is that not all events may be logged. For example, because it is a script trigger, you would then have to manually ensure the logging script would fire during a record delete or record creation. One good use case for this sort of logging is an auto-enter situation, in which changing field A will update field B. Having the script trigger turned on means that this modification will be automatically logged.

OnWindowTransaction

Newer versions of FileMaker can take advantage of the OnWindowTransaction trigger. Because this trigger works on the file level, it natively supports the ability to log record creates, modifications and deletes for single and multiple records across multiple contexts. It is also triggered in both Browse mode and Find mode. The logging script would function the same way as with the OnObjectSave or OnRecordCommit script triggers and wouldn’t need to be manually added to any create or delete scripts. A good use case for this script trigger would be an Order layout that interacts with data from several contexts, such as a quote table, an invoice table, and a line-item table.

One drawback to note about both options is that they are not triggered by FileMaker Data API and OData events, but the OnWindowTransaction event can be triggered by scripts run using the Data API or OData.

Having your logging script run within the file itself can provide some performance issues, so it is often good practice to take advantage of FileMaker’s PSOS (Perform Script on Server) capabilities. Allowing the server to do most of the work for creating your audit logs can assist with alleviating performance issues that might occur. Sometimes, it may be worth offloading the audit logs into another file entirely to reduce the bloat on the main system and provide another level of data integrity should the main file ever be corrupted.

Third-Party Solutions

Several third-party solutions offer advanced audit logging capabilities for FileMaker, particularly those that use JSON for change logs. Here are some prominent options:

1. BaseElements

Many of the functions within BaseElements aim to expand the capabilities of native FileMaker. For example, FileMaker’s native ExecuteSQL() function supports only reading information with a “select” statement. However, the BE_FileMakerSQL function supports a SQL query of any type, so it would be possible to use the BE_FileMakerSQL to call an “insert” statement to create a new record in an audit log table. It’s important to note the performance drawbacks of using SQL in any FileMaker system, however.

Another BaseElements script that can be leveraged for audit logging would be the BE_FileWriteText script. This script supports writing to either a file in a container field, or a specified file path. If you used a system that was structured to use an external JSON file for storing its audit logs, this script would be able to append a JSON audit log entry directly to that file and viewable outside of FileMaker. It could then be balanced with the script BE_FileReadText to be able to re-read audit log information within the FileMaker file. Like writing to an entirely separate FileMaker file than the one that is auditing, it’s fantastic for data integrity and minimizing bloat-related performance issues to be able to write outside of the main system. However, it’s also important to keep in mind that keeping a log outside of your FileMaker solution could also mean a risk of less accurate logs, so any steps taken to use this script should be balanced with strong error handling.

As a free plugin, BaseElements has a lower implementation cost compared to other third-party options. However, this can sometimes be offset by the time that is required to thoroughly understand how BaseElements can best be used in your FileMaker solution.

2. FM AuditLog Pro 2.0

One drawback to audit logging that many solutions must keep in mind is the impact it undoubtedly has on performance. However, FM AuditLog Pro 2.0 is a tool that puts minimizing its performance impact at its center. Designed in such a way to have minimal performance impact wherever it is run, it is also optimized to leverage the performance benefits of using FileMaker Server and PSOS (Perform Script on Server) scripts to execute its logging functions.

An important piece of FM AuditLog Pro 2.0 is that it is configured at the table level. This is done to be able to offer rollback capabilities, as it promises peak data integrity. Unlike many other solutions, FM AuditLog Pro 2.0 allows users to roll back both creates and deletes of records. It’s important to note, though, that this solution does not actually log the deletion of records despite its rollback abilities. This feature is enhanced by the timeline view of an audit log, allowing administrators to efficiently review any changes the data has undergone, thus being able to revert the record to a specific point in time.

While FM AuditLog Pro 2.0 is a paid solution, it is designed to be integrated with native FileMaker. With audit logging as its entire purpose, it offers several powerful features that would otherwise take considerable time and configuration to reproduce.

3. elemental_log

When logging in FileMaker, it can be important to take into account all the different ways that the data can be manipulated. With elemental_log, this is considered, as it creates JSON-based logs from all ways a field can be changed such as auto-enters, replaces, imports, data API calls, Custom Web Publishing, and more. In native FileMaker, it can be especially tricky to log events such as replaces and imports without field-level auto-enter, so having a native ability to log these events makes elemental_log powerful.

Another extremely beneficial feature of elemental_log is its ability to switch between viewing the JSON data for a log and a record view. This can be useful for database administrators who may not be familiar with JSON, but even more powerfully, it can be used in communicating with users of a system. In the event of misuse of data, it is much easier for a user to understand a record-based view of the changes they had made to the system than a chunk of JSON code, and it can be used to facilitate a conversation to prevent future data issues.

Being able to view the data changes in its own context can also be helpful for truly understanding the changes the data underwent. Instead of having to sift through mountains of overall logs, elemental_log makes it easy to look at an individual record. It provides the ability to look at the related information that can change alongside that record, providing a powerful glance at how a FileMaker solution’s data may all be related.

Designed to be easy to use and set up, elemental_log is a great solution for database administrators who may need a more user-friendly appearance rather than strict data information. It is a paid solution, and though it supports tracking and logging in multiple files, it is heavily encouraged to use only one instance of elemental_log per solution. It communicates with files by using scripts in your solution to call elemental_log, and vice versa, so good security practices must also be considered when implementing this into your FileMaker solution.

Conclusion

Audit logging in Claris FileMaker is crucial for maintaining database security, integrity, and compliance by providing a detailed history of user activities. Implementing audit logs requires careful consideration of many elements such as what data to log and managing performance impacts. Native FileMaker capabilities offer flexible solutions, while third-party options like BaseElements, FM AuditLog Pro 2.0, and elemental_log provide advanced features such as real-time tracking, rollback capabilities, and user-friendly interfaces. Choosing the right solution depends on your specific needs, but a well-implemented audit logging strategy enhances the security, reliability, and usability of FileMaker systems.

If you have questions or need help determining the best audit logging solution for you, please contact us at DB Services and we would be happy to assist you!