Working with Databases

Database Backup

Backing Up the Built-in File-Based Database

The Operavix application server performs a daily backup of the built-in file-based database at 12 AM. By default, backups are saved to the system folder: C:/ProgramData/Operavix/backup. The backup directory can be customized.

Database parameters are configured in the com.operavix.platform.component.database.json file. The default file path for the file is C:/ProgramData/Operavix/config/. Explanation for file parameters:

• "periodical_backup_enabled":true/false — determines whether periodic backups are enabled
• "backup_path" — specifies the location for database backups (relative to the ProgramData folder)

Backing Up ClickHouse (Docker Container)

ClickHouse database backup is not supported by the system's native tools. Data stored in ClickHouse can be backed up by copying the entire volume of the Docker container where the ClickHouse database is located.

Run this command to download the image:

# docker pull ubuntu:20.04

To perform a backup:

1. Create a new folder to store the backup:
   mkdir /tmp/clickhouse-backup
   

2. Remove the service:
   docker service rm operavix-clickhouse
   

3. Run a new container with volume mounted:
   docker run -it --rm --mount source=operavix-clickhouse,target=/clickhouse -v /tmp:/target ubuntu:20.04 bash
   

4. Inside the container, execute:
   tar -czvf /target/operavix-clickhouse.tar.gz /clickhouse
   

5. Exit the container:
   exit
   

6. Recreate the service. First, download the required image (link provided separately). Run the command:
   docker load -i operavix-clickhouse-(filename).tar.gz
   

7. The created backup is located at:
   • /tmp/operavix-clickhouse.tar.gz

Backing Up Activity Data

To back up activity data, go to GraphiQL and perform the following steps:

• If you need to back up data grouped by week, use this GraphiQL query:

{
    clickhouse {
        extract_data(storage_guid:"<storage_guid>",sql_script: "select id, `time`,  employee_account_id, time_offset, program, version, executable_path, `type`,   input_type, main_window, tab, url, `domain`, url_path, file_path, file_name,  toJSONString(location), toJSONString(location_name), toJSONString  (location_type), element_name, element_ctrl, element_hotkey_name, toJSONString  (parameters_name), toJSONString(parameters_value), toJSONString (parameters_type), cpu_loading, memory_loading, computer_name, agent_version   from `main`.monitoring_activity where toWeek(time) = 8 and toYear(time)=2024")
    }
}

Explanation:

• storage_guid — the data storage identifier
• 8 — the week number, 2024 — the year
• If you need to back up data grouped by month, use the second GraphQL query variant:

{
    clickhouse {
        extract_data(storage_guid:"<storage_guid>",sql_script: "select id, `time`, employee_account_id, time_offset, program, version, executable_path, `type`, input_type, main_window, tab, url, `domain`, url_path, file_path, file_name, toJSONString(location), toJSONString(location_name), toJSONString(location_type), element_name, element_ctrl, element_hotkey_name, toJSONString(parameters_name), toJSONString(parameters_value), toJSONString(parameters_type), cpu_loading, memory_loading, computer_name, agent_version from `main`.monitoring_activity where toMonth(time) = 1 and toYear(time) = 2024")
    }
}

Explanation:

• storage_guid — the data storage identifier
• 1 — the month number (in this example, January), 2024 — the year

After selecting the appropriate GraphQL query for data backup and replacing the parameters (week/month and year), change the URL in the browser address bar from _…/graphiql?query_ to _…/graphql?query…_ — remove the letter "i" from "graphiql". Press Enter to execute the query and initiate archive generation on the server. Once processed, the server will send the activity archive for the specified period.

Database Integrity Check

Integrity check for the built-in file-based database is performed on every system startup. Integrity is verified by checking the checksum against an already known checksum value. No integrity checks are performed during runtime. The system includes debugging tools that cannot be removed but can be controlled. To do so, monitor all changes to the Operavix service, particularly the startup command.

ClickHouse database integrity check is included with the Operavix system.

Saving an Anonymized Database Copy

To save an anonymized copy of the database, the user must have a role with the General Settings privilege enabled and operation W (write).

To save an anonymized database, log in via GraphQL and run the following mutation, specifying the target directory where the anonymized database will be saved:

mutation{
    database{
        copy_depersonalized_database(path:"c:/database")
    }
}

"c:/database" is the path to save the anonymized database.

To execute the GraphQL mutation, the user must have a role with the GraphQL Tool privilege enabled and operation E (execute).

The anonymized database is saved on the server where the system is installed. The target directory must be created beforehand and must be empty.