A Comprehensive Guide

Calvin Mosiman, Apr 2, 2024, FileMaker Training Videos

Introduction

In today’s data-driven world, accountability, traceability, and security are crucial elements of any database solution. FileMaker’s built-in audit log capabilities make it easier than ever to track, log, and review changes made within your database in a systematic and secure way. This comprehensive guide will walk you through how to leverage these audit logs for increased transparency and control over your data.

Understanding Audit Logs

An audit log, also known as an audit trail, is a record that tracks actions within a database. This could include inserting, updating, or deleting records. Each log entry typically records key information:

Who made the change (user account or device),
What change was made (the specific fields or records altered),
When it occurred (timestamp), and
How the change was executed (the action taken, such as modifying or deleting data).

Audit logs are crucial in ensuring the integrity and transparency of your data. They are essential for sectors like healthcare, banking, and governmental organizations where regulatory compliance requires accurate tracking of all changes.

FileMaker’s Built-in Audit Log Mechanism

Introduction to the OnWindowTransaction Script Trigger

In FileMaker’s recent versions, the OnWindowTransaction script trigger provides a native way to track database changes. Unlike traditional audit log setups that might require custom scripts for each layout or field, the OnWindowTransaction trigger operates at a file level, meaning it works globally across all layouts and windows in a FileMaker solution.

The trigger executes every time a transaction is committed, which occurs when data is saved to the FileMaker Server. This is a key aspect of atomic database transactions, where all operations within a transaction are completed or none at all—an “all or nothing” approach.

How the Audit Log System Works

FileMaker’s audit logging system is built on top of this powerful OnWindowTransaction trigger. When enabled, it captures all relevant transaction details such as:

File name: Which FileMaker file was being used.
Table name: The specific table in which the action occurred.
Action type: Whether the action was an insert, modify, or delete.
Record ID: The unique ID of the record being altered.
Modified fields: A list of all fields that were changed during the transaction.
Field values: Both the old and new values of the fields that were changed (if desired).

The captured data can be stored in an audit log table within the FileMaker solution, where it is available for querying, reporting, or troubleshooting purposes.

Understanding JSON in Audit Logging

A key feature of FileMaker’s audit log system is its use of JSON to structure the data captured during each transaction. JSON (JavaScript Object Notation) is a lightweight data-interchange format that is both human-readable and easy for machines to parse and generate.

In FileMaker, each transaction is recorded as a JSON object, which contains:

The file name and table name.
A list of fields that were modified, along with their old and new values.
Metadata about the transaction, such as the timestamp, the user’s account name, the user’s IP address, and more.

This structured format makes it easy to work with the data programmatically, allowing developers to filter, manipulate, and display audit logs in various ways.

Tracking Different Actions: Inserts, Modifications, and Deletions

The OnWindowTransaction trigger works across all types of database actions, including:

Inserts: When new records are created.
Modifications: When existing records are edited.
Deletions: When records are removed from the database.

Each of these actions is logged separately, making it easy to track what happened to any given record over time. For instance, you can see when a record was created, how it was modified (field by field), and when it was eventually deleted.

Setting Up Audit Logs in FileMaker

Setting up audit logs in FileMaker is surprisingly straightforward, especially if you use the OnWindowTransaction trigger and JSON-based data storage. Follow the steps below to get started.

Step-by-Step Setup of the Audit Log

Install the Audit Log Add-On (optional): If you’re new to audit logs or want to save time, FileMaker’s add-on makes the process even simpler. This add-on comes with the necessary table, fields, and scripts to get started quickly.
Create an Audit Log Table: If you choose to manually set up the audit log, create a new table called AuditLog in your database. This table should have fields for:
- Timestamp: The time when the transaction occurred.
- Table Name: The name of the table where the change happened.
- Record ID: The unique identifier for the record that was changed.
- Field Names: A list of fields that were modified.
- Field Values: The new values for each modified field.
Set Up the OnWindowTransaction Trigger:
- Open File Options in your FileMaker file.
- Select Script Triggers and scroll to the OnWindowTransaction trigger.
- Attach a script that will capture the transaction details and log them in your AuditLog table.
Design the Logging Script: Your script will run each time a transaction is committed. It should:
- Capture the JSON object containing the transaction details.
- Parse the JSON to extract relevant information (e.g., record ID, modified fields).
- Write the data into the AuditLog table for future review.

Enabling the OnWindowTransaction Trigger

The OnWindowTransaction trigger is an event-driven trigger that runs after a transaction is successfully committed. Enabling it ensures that the trigger executes every time data is saved to the FileMaker server, providing a comprehensive audit of changes across your database.

Once the trigger is enabled, you can configure it to pass a script parameter (the JSON object) to the audit log script. This parameter contains all the relevant information about the transaction, including the table name, record ID, and modified fields.

Audit Log Customization for Different Tables

For each table where you want to track changes, you need to create a custom field (e.g., Z_OnWindowTransaction). This field stores the audit log data and serves as the script parameter for the OnWindowTransaction trigger.

Each table’s audit log can be customized to log only the fields you care about. For instance, you might exclude calculated fields or global fields from the audit log, since they don’t store persistent data.

Adding the Audit Log to Your File

If you’re using the FileMaker Add-On, adding the audit log functionality to your file is as simple as dragging and dropping the add-on into your solution. This automatically installs the necessary scripts, custom functions, and table for logging audit data.

Once the add-on is installed, you can further customize the audit log to meet your specific needs, such as tracking additional fields or including more metadata (e.g., user IP address, machine name, etc.).

Advanced Configuration Options for Audit Logs

Filtering Fields for More Precise Auditing

Not every field in your database needs to be logged. For example, utility fields like sorting fields, calculated fields, or summary fields may not provide meaningful audit data. To optimize your audit log and avoid unnecessary clutter, you can filter which fields are logged.

FileMaker allows you to apply custom filtering logic in your audit script. You can:

Exclude fields with specific prefixes (e.g., fields starting with z_ or c_).
Filter out calculated fields and global fields, which often change frequently but don’t store meaningful transactional data.
Only log fields that are essential for tracking significant changes.

Audit Log Data Storage Considerations

For large databases or heavily used solutions, audit logs can grow rapidly. You have several options for managing the size of your logs:

Storing logs in a separate table: You can store audit data in its own table to reduce clutter in your main tables.
Using an external audit log file: For more complex solutions, consider storing audit logs in a separate FileMaker file. This can improve performance by offloading the audit data from your primary database.
Archiving old logs: You can periodically move older logs to an archive table or file to keep your audit logs manageable.

Tracking Deleted Records

One of the most important features of an audit log is its ability to track deleted records. When a record is deleted, it can be difficult to reconstruct what data was lost. FileMaker’s audit log can capture the state of the record before it was deleted, ensuring that you still have a record of the data even after it’s removed from the main table.

To log deleted records, ensure that your OnWindowTransaction script captures the state of the record before deletion and logs it in the audit table. This way, you can always trace back what data was deleted and when.

Handling Large Audit Logs: Archiving and Performance

As your audit logs grow, performance can become a concern. If you’re logging every transaction in a heavily used solution, the audit log can quickly become a large file. To maintain performance:

Archive older records periodically by moving them to a separate file or table.
Delete unnecessary logs: Set up a script to automatically delete audit logs after a certain period (e.g., 90 days) if they are no longer needed.
Monitor audit log size: Regularly check the size of your audit log and archive or delete logs to prevent performance degradation.

Practical Use Cases for FileMaker Audit Logs

Compliance in Regulated Industries

For industries such as healthcare, finance, and government, audit logs are often a requirement for regulatory compliance. FileMaker’s audit logging system ensures you meet these compliance standards by providing a complete record of every change made to your data.

Audit logs are especially useful for compliance with regulations such as:

HIPAA (Health Insurance Portability and Accountability Act): Audit logs track who accessed patient records and what changes were made, ensuring accountability.
GDPR (General Data Protection Regulation): Audit logs track data modifications, deletions, and user access to personal data, helping organizations comply with strict data protection laws.
SOX (Sarbanes-Oxley Act): Audit logs provide transparency in financial systems, ensuring accountability for financial data changes.

Enhanced Debugging and Troubleshooting

Audit logs are also valuable for debugging. When users report issues or unexpected data changes, the audit log provides a detailed history of what happened, allowing developers to quickly identify and fix the problem.

For example, if a record’s data seems incorrect, you can use the audit log to trace back:

Who modified the record,
When the modification occurred, and
What values were changed.

This detailed history can significantly reduce the time spent troubleshooting data issues.

Improving Data Security

Audit logs provide an additional layer of data security by ensuring that every modification is tracked. In the event of unauthorized changes, the audit log provides a forensic trail that can help identify the source of the breach.

For example, if a malicious actor gains access to the database and makes changes, the audit log will record:

The user account that made the changes,
The IP address or machine from which the changes originated, and
The specific fields that were altered.

Optimizing User Accountability and Role-Based Access

In large organizations where multiple users have access to the same database, it’s essential to track user activity to ensure accountability. Audit logs make it easy to see:

Who made changes to which records,
Whether the changes were intentional or accidental, and
Whether the user had the correct permissions to make those changes.

By combining audit logs with role-based access controls (RBAC), organizations can ensure that only authorized users can make changes to sensitive data, and that every change is fully documented.

Best Practices for FileMaker Audit Logs

Performance Optimization

As you implement audit logs, it’s important to ensure that they don’t negatively impact the performance of your database. Here are some tips for optimizing performance:

Filter unnecessary fields: Only log changes to important fields to reduce the size of your audit logs.
Use external log files: For large solutions, consider storing audit logs in a separate FileMaker file to reduce the load on your main database.
Limit the size of your logs: Regularly archive or delete older logs to keep the audit table manageable.

Log Retention Strategies

To avoid overwhelming your database with audit log data, establish a clear log retention policy. Depending on your organization’s needs, you might choose to:

Archive logs after a set period (e.g., 1 year).
Delete logs that are no longer needed (e.g., logs older than 90 days).
Export logs to external systems for long-term storage and analysis.

User Access and Security

Audit logs contain sensitive data, so it’s important to control who has access to them. Best practices include:

Restricting access to audit logs to administrators and authorized personnel.
Encrypting audit logs if they contain sensitive information.
Monitoring access to the logs themselves, ensuring that even access to the audit log is logged for accountability.

Conclusion

FileMaker’s built-in audit log system offers a powerful, flexible, and easy-to-implement solution for tracking changes within your database. Whether you’re aiming for regulatory compliance, improved data security, or enhanced troubleshooting capabilities, FileMaker’s audit logs provide the transparency and control you need.

By leveraging the OnWindowTransaction trigger, structured JSON logging, and careful field filtering, you can create a comprehensive audit trail that supports your organization’s needs—ensuring that every action taken in your database is fully accountable.

Sample File – Audit Log

FileMaker’s Built-in Audit Logs