Report Database Settings

Cerberus FTP Server Enterprise edition supports collecting and reporting detailed session and file access statistics using an ODBC-compliant database. A database connection must be configured in Cerberus before the server will begin collecting statistics. The reporting database connection will also be used by the Reporting Manager for generating reports.

The following databases are currently supported (others may work with appropriate ODBC driver installed):

• Microsoft SQL Server 2014 Enterprise and higher
• Microsoft SQL Express LocalDB 2014 and higher
• Microsoft Azure SQL Server
• MySQL Server 5.2 and higher
• PostgreSQL version 10 to version 13
  

Connecting to a Database for Cerberus

New Database Connection:

Setting up a new database connection for Cerberus involves two steps:

1. Enable Statistics Collection: This helps Cerberus track database performance and optimize data access.
2. Choose a Database Driver: This tells Cerberus how to interact with your chosen database software.

Recommendations:

• Most Cerberus Users: For most situations, we recommend using the free SQL Express LocalDB database. It's easy to set up and has enough capacity for most Cerberus installations.
• High-Volume Installations: If you have a very high volume of data in your Cerberus installation, a full Microsoft SQL Database might be a better option. You can purchase or connect to an existing full SQL database.

Instructions:

Below you'll find instructions for:

• Installing and connecting to SQL Express LocalDB (recommended for most users)
• Connecting to a full Microsoft SQL Database (for high-volume installations)

Installing SQL Express LocalDB (Recommended for Most Users)

The quickest and easiest database option is Microsoft Server Express LocalDB. LocalDB is a lightweight, embedded database option from Microsoft that is suitable for local, low-utilization database traffic. It has a relatively small footprint and installs quickly. You will also need the SQL Server Native Client 11.0 ODBC driver for connecting to LocalDB

Important: If you are running the 64-bit version of Cerberus FTP Server, make sure you download and install the 64-bit version of SQL LocalDB and the corresponding 64-bit SQL Server 2014 Native Client driver. The same applies to the 32-bit version as well. 

After installing SQL Server Express LocalDB and the SQL Server Native Client, you can go to the Reporting page and select the SQL Server Native Client 11.0 driver.

1. Click Reporting on the Cerberus FTP Server menu to open the Report Manager page, then select Database Configuration.
2. In the Drivers, field select SQL Server Native Client 11.0
3. The connection parameters available for your driver type will now appear. Select LocalDB 20xx for a Microsoft SQL Server 20xx LocalDB installation, where 20xx is the version matching what you installed (2014 for LocalDB 2014 and higher).
4. Press the Connect button to test your connection.
5. If there are no ODBC Connection errors after pressing the Connect button, press the Create Tables button to create the necessary database tables for Cerberus to write to the database.
6. Click the Enable Statistics Collection slider to turn on statistics collection.
7. Press Update to save changes.

Connecting to an existing SQL Database

To connect to an existing SQL Database, follow the outlined steps below. Note that the ODBC database driver and specific driver settings may vary depending on the database.

1. Open the Cerberus FTP Server menu and click on Reporting to access the Report Manager page.
2. Select the Database Configuration tab.
3. In the Drivers select box, choose the ODBC database driver suitable for your database type (e.g., ODBC Driver 17 for SQL Server for a remote connection to Microsoft SQL Server).
   • For Microsoft SQL Server installations other than LocalDB, it's recommended to download and install the Microsoft ODBC Driver 17 for SQL Server, as certain reporting features may not function properly with the default SQL Server ODBC driver on most machines.
4. Fill in the connection parameters specific to your chosen ODBC driver.
5. After providing the ODBC driver connection parameters, click the Connect button to test the connection. This action verifies Cerberus can establish a connection to the database and conducts a quick search for the necessary Cerberus statistics tables.
6. The result of the ODBC Connection should display "Success," and entries for different database tables should be visible. If tables haven't been created yet, error messages will be displayed next to each table entry. If your database requires a username and password, the user account must have permission to create a database and tables within it; otherwise, statistics collection and reporting won't function correctly.
7. Click the Create Tables button to generate the essential database tables for Cerberus to write to.
8. If the table creation is successful, you will see each table listed in the status box with the message "Created" next to it, indicating that your connection setup is complete.
9. Click the Enable Statistics Collection slider to turn on statistics collection.
10. Restart the Cerberus service using 'services.msc' to ensure the change in statistics collection status is enabled. If you turn Statistics Collection OFF, you must also restart the service for the collection to stop.

Create and Connect to a new SQL Database

To create and connect to a new SQL Database, follow the outlined steps below. Note that the ODBC database driver and specific driver settings may vary depending on the database.

1. Create a blank database on the DB server called cerberus_stats
2. Open the Cerberus FTP Server menu and click on Reporting to access the Report Manager page.
3. Select the Database Configuration tab.
4. In the Drivers select box, choose the ODBC database driver suitable for your database type (e.g., ODBC Driver 17 for SQL Server for a remote connection to Microsoft SQL Server).
   • For Microsoft SQL Server installations other than LocalDB, it's recommended to download and install the Microsoft ODBC Driver 17 for SQL Server, as certain reporting features may not function properly with the default SQL Server ODBC driver on most machines.
5. Fill in the connection parameters specific to your chosen ODBC driver.
6. After providing the ODBC driver connection parameters, click the Create Tables button to generate the essential database tables for Cerberus to write to.
7. After the tables have been created, click the Connect button to test the connection. This action verifies Cerberus can establish a connection to the database and conducts a quick search for the necessary Cerberus statistics tables.
8. The result of the ODBC Connection should display Success, and entries for different database tables should be visible. If tables haven't been created yet, error messages will be displayed next to each table entry. If your database requires a username and password, the user account must have permission to create a database and tables within it; otherwise, statistics collection and reporting won't function correctly.
9. If the table creation is successful, you will see each table listed in the status box with the message "Created" next to it, indicating that your connection setup is complete.
10. Click the Enable Statistics Collection slider to turn on statistics collection.
11. Restart the Cerberus service using 'services.msc' to ensure the change in statistics collection status is enabled. If you turn Statistics Collection OFF, you must also restart the service for the collection to stop.

Clean Tables

The "Clean Tables" feature on the Database Configuration tab provides a convenient method for eliminating older records from your database. The objective is to purge records from the database that exceed the specified time interval, contributing to overall database maintenance. When you choose an option from the "Clean Tables" drop-down menu, it will initiate the deletion of Audit Log and File Operation records older than 1 month/6 months/1 year/2 years. Additionally, it will remove any file records lacking corresponding file operations.

The time periods are determined based on the calendar. For instance, if you initiate a 1-month cleanup on March 31, 2021, at 1:01:00 pm, the cleanup will occur before February 28, 2021, at 1:01:00 pm. Similarly, a 6-month cleanup requested on September 1, 2021, at 10:00:57 am will be completed before March 1, 2021, at 10:00:57 am.

Once you choose a time frame (e.g., 2 years in this example), you will receive a confirmation dialog before the actual database operation is executed.

Upon confirmation, audit records are generated and recorded to maintain a record of users performing database cleanups. This serves as a reference in case there are inquiries about when a specific activity took place and who carried it out. Details regarding the delete operation will be inserted into the database and simultaneously displayed on the Database Configuration UI screen.

By choosing "Connect" once more to gather statistics on the current database state, we can observe the outcomes. In this scenario, six orphaned file entries were successfully removed, and the count of unique files decreased from 58 to 52. Furthermore, three new user activity or audit records have been added, reflecting the three types of deletions triggered by the delete operation.

Cerberus FTP Server logs corresponding messages in the output logs, providing a reference for when deletion attempts took place and identifying the user responsible for the actions.