An In-Depth Guide

Taylor Sharpe, Feb 28, 2023, Kentuckiana FM Dev Group

Menu

• Introduction
• Why Audit Logs Are Essential
• Understanding FileMaker’s Native Capabilities
• Why Use JSON for Audit Logging?
• Structuring JSON for FileMaker Audit Logs
• Tracking Deletions: Implementing a Delete Script
• Custom Functions for Efficient Audit Logging
• Managing Performance and Scalability
• The Importance of Security and User Privileges
• Audit Logging Challenges and Considerations
• Advanced Techniques: Rollbacks and Record Recovery
• Conclusion

Introduction

Audit logging is an essential aspect of any modern FileMaker solution, especially for businesses concerned with security, compliance, or workflow optimization. It allows developers to track the changes made to records, providing a detailed history of who changed what and when.

In this guide, we will dive deep into how to set up audit logging in FileMaker using JSON—a lightweight, versatile format that integrates seamlessly into FileMaker’s native functions. We’ll cover everything from the importance of audit logs, to tracking changes and deletions, to managing performance impacts. By the end, you’ll have a comprehensive understanding of how to build a robust audit trail in your FileMaker applications.

Why Audit Logs Are Essential

Audit logs are not just for fraud prevention—although that is certainly one of the key selling points. They also serve a broader purpose of maintaining data integrity, improving user accountability, and supporting user training. Here’s why audit logs are invaluable:

1. Fraud Detection and Prevention

Audit logs allow decision-makers to monitor suspicious activity, like unauthorized data deletion or modification. For example, if an employee attempts to delete a financial record to hide theft, the audit log will capture the exact details, making it possible to trace back and understand what happened.

2. Data Integrity and Restoration

In many cases, audit logs help identify accidental changes that need to be reversed. Mistakes happen, and with a well-structured audit trail, it becomes easy to determine what the correct value should be and restore it.

3. User Training and Monitoring

A common issue in businesses is improper use of the system. Audit logs enable administrators to monitor which users are consistently making errors. Instead of using the logs solely for punitive measures, they can also be leveraged to improve training programs and ensure users are following best practices.

4. Compliance with Legal and Regulatory Requirements

Certain industries require audit trails to meet compliance standards such as GDPR, HIPAA, or SOX. A well-designed audit system helps ensure that your FileMaker solution adheres to these standards, mitigating legal risks and ensuring transparency.

Understanding FileMaker’s Native Capabilities

FileMaker offers some built-in tools for tracking modifications, such as Modification Timestamp, Modification Account Name, and Creation Timestamp. However, these fields only retain the most recent changes, which means they don’t provide a complete history of all modifications.

Additionally, deletions are not tracked by these native fields, leaving a significant gap in traceability. If users have the ability to delete records, without an audit trail, the entire history of a record can vanish without a trace.

Traditional Methods:

• Creation Date & User: Useful for knowing who created a record.
• Modification Date & User: Helps track the most recent changes but lacks historical depth.

For a more comprehensive system, we need to build a custom audit trail.

Why Use JSON for Audit Logging?

JSON (JavaScript Object Notation) is a compact, lightweight format that is ideal for storing structured data. FileMaker’s native support for JSON makes it an excellent choice for building audit logs that can handle complex data changes while remaining easy to manage and parse.

Benefits of JSON for Audit Logging:

1. Structured Data: JSON organizes data into key-value pairs, making it easy to store and retrieve specific changes (e.g., which fields were updated and what the old values were).
2. Compact Format: Unlike creating multiple fields or records to store changes, JSON allows you to store all changes in a single field, which reduces overhead.
3. Human-Readable: JSON is easy for developers to read and interpret, making troubleshooting and auditing more manageable.
4. Built-In Functions: FileMaker’s native support for JSON includes a wide array of functions (JSONSetElement, JSONGetElement, JSONListKeys, etc.), making it easy to work with JSON data directly within your scripts.

Structuring JSON for FileMaker Audit Logs

The core of your audit logging system is how you structure your JSON to capture relevant data. When a user modifies a record, we want to log not only the new values but also the previous values, along with metadata such as the user who made the change, the time of the change, and the script that triggered it (if applicable).

Here is a sample JSON structure for tracking field changes:

jsonCopy code{
  "modification_timestamp": "2024-10-01T12:34:56Z",
  "modified_fields": {
    "customer_name": {
      "old_value": "John Doe",
      "new_value": "Jane Smith"
    },
    "invoice_total": {
      "old_value": 1500,
      "new_value": 2000
    }
  },
  "user": {
    "account_name": "admin",
    "device_name": "MacBook Pro",
    "filemaker_version": "21.0.1"
  },
  "metadata": {
    "script": "Update Invoice",
    "privilege_set": "Full Access",
    "ip_address": "192.168.1.5"
  }
}

This structure contains all the essential details:

• Timestamp: The date and time of the modification.
• Modified Fields: A breakdown of which fields were changed, along with both the old and new values.
• User Information: The user account, device, and FileMaker version used.
• Additional Metadata: Optional data, such as the name of the script that triggered the change and the user’s IP address.

By using this structure, you can create a rich and detailed log of every modification to your records.

Tracking Deletions: Implementing a Delete Script

One of the most challenging aspects of audit logging is tracking deletions. Without a proper deletion tracking system, important data could be erased from the database with no way of knowing it ever existed. The solution is to override the default delete functionality and use a custom script to handle all deletions.

Step 1: Create a “Deletes” Table

This table will store a JSON representation of each deleted record, including the data that was deleted and metadata about the deletion event.

Step 2: Capture Deletion Data

When a user attempts to delete a record, instead of directly deleting it, your script will:

1. Store the current record data in JSON format.
2. Log the user and timestamp of the deletion.
3. Insert this data into the “Deletes” table.
4. Delete the record from the main table.

Sample Delete Script:

filemakerCopy code# Capture record data before deletion
Set Variable [$recordData; Value: JSONFormatElements(
  JSONSetElement ( "" ;
  ["record_id"; table::primaryKey] ;
  ["customer_name"; table::customer_name] ;
  ["invoice_total"; table::invoice_total] ;
  ["deleted_by"; Get(AccountName)] ;
  ["timestamp"; Get(CurrentTimestamp)] ;
  "" ))]

# Log deletion in the "Deletes" table
Go to Layout [“Deletes”]
New Record/Request
Set Field [Deletes::record_data; $recordData]

# Delete the original record
Go to Layout [“Original Table”]
Delete Record [No dialog]

This script ensures that all deleted records are captured and logged before they are permanently removed from the system.

Custom Functions for Efficient Audit Logging

To streamline audit logging, custom functions can be employed to automate the process of capturing changes. One such custom function is TMS_History, which handles the complex logic of capturing field changes, user information, and timestamps, and storing them as JSON.

The Role of TMS_History:

The TMS_History function makes it easy to track changes across all tables in your FileMaker solution. It automatically captures:

• The primary key of the modified record.
• Which fields were modified.
• The user who made the modification.
• The device and FileMaker version used.
• Additional metadata like script names, privilege sets, and IP addresses.

Parameters for TMS_History:

• Primary Key: Used to identify the record being logged.
• Modified Fields: A list of fields that were changed.
• Omitted Fields: Fields that should be excluded from the audit trail (e.g., large text fields or container fields).
• Max Saves

: Limits how many historical entries are saved to prevent excessive data storage.

Example Usage:

filemakerCopy codeSet Field [table::audit_field; TMS_History (table::primaryKey, table::modifiedFields)]

This custom function simplifies the entire process of capturing modifications and ensures consistency across your solution.

Managing Performance and Scalability

One of the major concerns when implementing audit logs is the performance impact. While audit logs provide invaluable data, they also introduce additional overhead in terms of storage and processing time. This is particularly important for large databases or high-traffic applications.

Best Practices for Managing Performance:

1. Limit Audit Logging to Key Fields: Only log changes to fields that are relevant to your audit trail. For example, avoid logging every update to large text fields or container fields.
2. Use Conditional Logging: Not every action needs to be logged. For instance, automated scripts that update summary fields every night may not need to be tracked.
3. Cap the Number of Historical Entries: Use the Max Saves parameter in the custom function to limit the number of historical changes saved. After a certain threshold (e.g., 100 changes), you can purge old entries to keep the system running smoothly.
4. External Storage: If your audit logs become too large to store efficiently within FileMaker, consider exporting them to external files (e.g., JSON or CSV) or storing them in a separate database.

The Importance of Security and User Privileges

Audit logs are only useful if they are secure. If users with full access can modify or delete audit logs, it undermines the entire system. Therefore, it’s critical to properly configure user privileges.

Key Security Practices:

1. Restrict Full Access Privileges: Only trusted developers should have full access to the solution. Users with full access can bypass audit logs, rendering them ineffective.
2. Lock Down the Audit Logs: Ensure that users cannot manually modify or delete records from the audit log. This can be done by using access privileges and controlling the ability to delete records.
3. Use Scripted Deletion: Ensure that users can only delete records through the custom deletion scripts that log the deletion event.
4. Encryption: Consider encrypting your audit logs to protect sensitive information, especially if the logs contain confidential data.

Audit Logging Challenges and Considerations

1. Performance Trade-offs

Audit logs introduce overhead, and depending on how your solution is designed, this can impact performance. Careful design and optimization can minimize the performance impact, but developers should always test the solution with real-world data volumes before deploying.

2. Handling Large Data Sets

As audit logs grow, they can become unwieldy. Consider implementing data archival solutions, exporting old logs, or building a process to archive logs periodically.

3. Deleted Record Restoration

One challenge with audit logs is determining whether a deleted record needs to be restored. Deleting records without an audit log makes it difficult to reconstruct what was deleted. With a deletes table, you can easily restore previously deleted records by capturing a complete JSON representation before the deletion occurs.

Advanced Techniques: Rollbacks and Record Recovery

One of the most valuable features of audit logs is the ability to roll back changes or restore deleted records. This is especially useful in cases of accidental deletions or erroneous data entry.

Rollback Functionality:

You can build a custom script to “roll back” a record to a previous state by:

1. Parsing the JSON history for the record.
2. Reversing changes made to fields by using the “old_value” in the JSON structure.
3. Restoring deleted records from the deletes table.

Example Rollback Script:

filemakerCopy code# Retrieve JSON history for the record
Set Variable [$history; Value: table::audit_history]

# Find the previous values
Set Variable [$previousValues; JSONGetElement($history; "old_value")]

# Update the record with previous values
Set Field [table::field1; JSONGetElement($previousValues; "field1")]
Set Field [table::field2; JSONGetElement($previousValues; "field2")]

This script allows you to restore the record to a previous state, which can be critical for maintaining data integrity.

Conclusion

Audit logging in FileMaker, especially when combined with JSON, provides a powerful solution for tracking changes, detecting fraud, improving data integrity, and supporting compliance. By implementing a detailed audit logging system with custom scripts, functions, and proper security, developers can ensure that their FileMaker solutions are not only robust but also capable of handling the complexities of modern data management.

While audit logging introduces some overhead, the benefits far outweigh the trade-offs, especially for businesses in regulated industries or those dealing with sensitive data. If implemented thoughtfully, JSON-based audit logging can become an invaluable tool for any FileMaker developer looking to enhance the security and transparency of their solutions.

https://fm.tms.us/fmi/webd/TMSsoftware