FileMaker IT Auditing Procedure

Dimitris Kokoutsidis, Sept 26, 2024, CyberFM

1. Audit Overview

Objective:
The goal of an audit is to ensure the reliability, security, and operational effectiveness of the FileMaker solution. The audit process involves identifying potential points of failure or security risks in the system and addressing them proactively.

Process:

• Understand the Solution’s Scope:
  Begin by documenting the entire FileMaker deployment, including:
  • Databases: List each FileMaker database file, noting its role in the system.
  • Server Infrastructure: Include hardware configurations such as CPU, RAM, disk space, and whether it is hosted locally or on a cloud platform like AWS.
  • Client Access: Document the types of clients accessing the solution (FileMaker Pro, WebDirect, Go, etc.).
  • External Integrations: Identify third-party integrations or services (e.g., APIs, plugins).
  • Key Layouts and Scripts: Identify mission-critical scripts and layouts that must be prioritized during troubleshooting or recovery.
• Define Critical Functions:
  List the scripts, processes, and schedules that are vital to daily business operations. Prioritize auditing these areas:
  • Scheduled Scripts: Ensure that server-side scheduled scripts are running on time and performing necessary tasks (e.g., data imports, backups).
  • Complex Calculations: Review complex formulas and relationships that might cause performance degradation over time.
• List Assets:
  Maintain a detailed asset inventory covering:
  • Server Hardware: Document operating system, RAM, CPU, storage, network configuration, and backup devices.
  • FileMaker Server Version: Record the exact version of FileMaker Server and any patches or custom configurations.
  • Licenses and Subscriptions: Log FileMaker licensing and any third-party software or services used.

Documentation Tips:
Maintain both physical and digital copies of all critical documents, ensuring they are easily accessible in the event of a server failure or staff change.

2. Backup Strategy and Verification

Objective:
Ensure that backups are not only regularly scheduled but also fully tested and ready for immediate restoration when needed.

Process:

• Schedule Backups:
  Implement a robust, layered backup strategy:
  • Daily Full Backups: Schedule at least one full backup daily, capturing the entire database including all records, settings, and system configurations.
  • Incremental Backups: Depending on database size and business needs, schedule incremental backups every 15 to 30 minutes to minimize data loss.
• Backup Frequency:
  Adjust backup frequency based on business needs:
  • Hourly for Critical Systems: Set up hourly backups for systems that cannot afford significant downtime or data loss.
  • Daily or Weekly for Low-Impact Data: Non-critical systems may only need daily or weekly backups, reducing strain on storage.
• Backup Locations:
  Diversify backup locations for maximum protection:
  • On-site Backups: Store local backups on a separate physical drive from the primary storage.
  • Off-site Backups: Automatically push backups to off-site locations, whether through cloud services like AWS S3 or physical storage at a secure location.
• Backup Types:
  • Verified Backups: Enable the Verify Backup option in FileMaker Server’s backup settings to ensure data integrity.
  • Clones vs Full Backups: Utilize clones (backups without data) during migrations or data structure comparisons, but rely on full backups for operational needs.
• Testing Backups:
  Test backups regularly:
  • Monthly Restorations: Restore backups on a test system at least once a month. Open the restored database in FileMaker Pro to confirm data integrity.
  • Check for Corruption: During the restore process, ensure no database corruption is present.
• Email Notifications:
  Configure email alerts for:
  • Backup Success: To confirm that backups are running as scheduled.
  • Backup Failure: Immediate alerts for failures enable prompt troubleshooting.

3. Disaster Recovery Planning

Objective:
Prepare for catastrophic failures by developing and testing a recovery plan to ensure quick restoration of services.

Process:

• Develop a Comprehensive Recovery Plan:
  Document the entire recovery process in clear, step-by-step instructions:
  • Primary Contacts: List contact information for internal IT staff, third-party FileMaker consultants, and stakeholders.
  • Recovery Instructions: Provide detailed instructions for stopping the server, restoring from backups, and verifying restored data.
  • Server Credentials: Securely store records of all server login credentials (SSH access, FileMaker Admin Console access, encryption keys).
• Testing Disaster Recovery:
  Test the recovery plan regularly:
  • Bi-Annual Mock Disaster: Conduct mock disaster recovery drills twice a year. Simulate a major failure and restore from backups, documenting any issues to refine the recovery process.
  • Identify Gaps: During testing, note any gaps or failures in the plan and update it accordingly.
• Failover and Hot Standby Servers:
  Implement failover systems for minimal disruption:
  • Hot Standby Server: Maintain a hot standby server that mirrors the primary system in real-time, using tools like 360Works MirrorSync to keep data synchronized.
  • Remote Hosting Options: For distributed teams, consider using cloud hosting with automatic load balancing and failover options.

4. Security Audits

Objective:
Protect the FileMaker system from unauthorized access, data theft, and other security risks.

Process:

• Review User Accounts:
  Conduct regular audits of user accounts:
  • Remove Inactive Accounts: Identify and deactivate any inactive accounts.
  • Verify Privilege Sets: Ensure users have access only to the areas necessary for their roles, applying least privilege principles.
• Password Policies:
  • Enforce Password Complexity: Require strong passwords with a combination of uppercase, lowercase, numbers, and symbols.
  • Regular Password Changes: Implement policies requiring password changes every 60–90 days.
  • Multi-Factor Authentication (MFA): Enforce MFA where possible to add an extra layer of security.
• Encryption:
  • Encryption at Rest: Use FileMaker’s built-in encryption to secure databases at rest. Store the encryption passphrase separately from the database.
  • SSL for Data in Transit: Always enable SSL on FileMaker Server to secure data transmission. Utilize Let’s Encrypt SSL certificates for automated renewal on cloud-hosted servers.
• Audit Logs:
  Regularly review audit logs to track user activity:
  • Track Suspicious Activity: Monitor for unusual login patterns or access to unauthorized areas.
  • Log Changes: Ensure that changes to critical records, layouts, or scripts are logged, providing a trail in case of data tampering.

5. Server Configuration and Performance Tuning

Objective:
Ensure FileMaker Server is configured optimally for performance, stability, and reliability.

Process:

• Monitor Resource Usage:
  Regularly check the CPU, memory, and disk usage of the server:
  • High Utilization Alerts: Set up monitoring tools to alert administrators if CPU or memory usage exceeds 80%.
  • Disk Space: Ensure at least 25% of disk space is free to avoid performance issues and database corruption, especially during backup operations.
• Cache Settings:
  Optimize FileMaker Server’s cache settings:
  • Cache Usage: Set cache usage to 50-80% of the available RAM.
  • Check Cache Hit Ratio: In the FileMaker Server Admin Console, monitor the cache hit ratio to ensure efficient use of cache memory.
• Optimize Layouts:
  Layouts can be a source of performance bottlenecks. To optimize them:
  • Reduce Unstored Calculations: Minimize the use of unstored calculations on layouts, which recalculates every time the layout is loaded.
  • Remove Unnecessary Summary Fields: Avoid overloading layouts with unnecessary summary fields.
  • Simplify Layouts: Reduce the number of objects or tabs to streamline rendering.

6. Testing and Monitoring

Objective:
Proactively monitor the health of the FileMaker system and test critical functions regularly.

Process:

• Use Server Logs:
  Set up automated log reviews:
  • Automated Log Review: Use tools or scripts to regularly review FileMaker Server logs for warnings or errors.
  • Set Threshold Alerts: Configure alerts to notify administrators if key performance thresholds (e.g., excessive query times, high CPU usage) are crossed.
• Regular System Health Checks:
  Schedule quarterly health checks, including:
  • Server Logs: Review logs for any signs of issues, such as failed scripts or backups.
  • Security Logs: Monitor login attempts and audit logs for unauthorized access or unusual activity.
• User Feedback:
  Gather regular user feedback to detect potential performance issues or unusual behavior.
• Monitor Network Latency:
  Use monitoring tools to ensure network latency between FileMaker clients and the server stays below 100 ms, as high latency can degrade performance.