Audit Log Export

Overview

QueryPie provides a feature to extract various audit logs and download them as CSV files. Even large files, such as those spanning long periods, can be extracted and downloaded stably. Once generated, log extraction files are available for download for 30 days. As of QueryPie v9.19.0, 18 types of logs are supported for extraction, including Query Audit and Workflow SQL Request.

With the addition of the Audit Log Export feature, the 'Excel File Download' button previously provided on each log screen has been discontinued starting from QueryPie v9.15.0.

Supported Log Types (as of QueryPie v9.19.0)

1. Query Audit [CSV]

2. Workflow SQL Request [CSV]

3. Workflow SQL Request for Query Details [CSV]

4. Workflow SQL Export Request [CSV]

5. Workflow DB Access Request [CSV]

6. Workflow Server Access Request [JSON]

7. User Access History [CSV]

8. Admin Role History [CSV]

9. DB Access History [CSV]

10. DB Account Lock History [CSV]

11. DB Access Control Logs [CSV]

12. Server Access History [JSON]

13. Command Audit [JSON]

14. Session Logs [JSON]

15. Server Access Control Logs [JSON]

16. Server Role History [JSON]

17. Activity Logs [JSON]

18. DML Snapshot [JSON]

Viewing the Audit Log Export List

1. Navigate to the Administrator > Audit > General > Audit Log Export menu.

2. View the list of generated audit log export tasks to date.

3. Check the status of tasks in the Status column:

   • Processing: The audit log extraction is in progress.

   • Completed: The audit log extraction is complete, and the file is available for download (files are available for 30 days after extraction).

   • Failed: The audit log extraction has failed. Please contact QueryPie Customer Support.

Creating a Log Extraction Task

Audit log extraction and download proceed through the following steps: (1) Access the relevant menu. → (2) Create a log extraction task. → (3) Wait for the extraction to complete. → (4) Download the file upon completion.

To start the audit log extraction, navigate to the Audit > General > Audit Log Export menu. Then, click the Create Task button at the top right. The following screen will appear:

1. Task Name: Enter the name of the log extraction task.

2. Log Type: Select the type of log to extract

   1. Select the type of log to extract (e.g., Query Audit).

   2. Click See Log Template and Description for detailed information about each log's keys and values.

3. Download File Format: Specify the output file format (as of v9.17.0, each log type has a single available format).

4. From: Specify the start date for the extraction period.

5. To: Specify the end date for the extraction period.

6. Filter Expression: Specify filter expressions.

   1. See Filter Expressions below for examples.

7. Generate Preview:

   1. This step is required.

   2. Review the preview to proceed to the next step.

8. Create: Generate the log extraction task.

Filter expressions

(1) To utilize filter expressions, refer to 'See Log Template and Description' for the log-specific keys, their types, and included values.

(2) Filter expressions are categorized based on data type:

• Number Type

  • Supported expressions : >, <, <=, >=, ==, !=

  • e.g. x > `10`, x == `10`

• String Type

  • Supported expressions : == (equals), != (not equals), contains

  • e.g. x == 'abc', x != 'abc', contains(x, 'ab')

• Boolean Type

  • Supported expressions : == (equals), != (not equals), && (and), || (or)

  • e.g. x == `true`, x && y, (x > `0`) && (y == `0`)

• Array Type

  • Example: x[? @ == 'value'], list[? @ > `10`]

(3) Use the following characters for multiple conditions :

• AND Condition : &&

• OR Condition : ||

• Compound condition : ( )

(4) Examples :

• Extract only query execution logs during Query Audit :

  • actionType == 'SQL_EXECUTION'

• Extract only query execution logs made to WebEditor during Query Audit :

  • actionType == 'SQL_EXECUTION' && executedFrom == 'WEB_EDITOR'

• Extract logs for two specific databases from DB Access History :

  • connectionName == 'database1' || connectionName == 'database2'

• Extract DB Access History for two specific databases with Replication Type as SINGLE :

  • (connectionName == 'database1' || connectionName == 'database2') && replicationType == 'SINGLE'

Criteria for specifying the Privilege Type of export files in Query Audit

The 'Privilege Type' column in the export file indicates the privileges required at runtime. It functions as follows:

1. Commands executed with default privileges (SET, SHOW, etc.) have a blank value in the corresponding column.

2. Logs executed with privileges such as INSERT specify the SQL Type.

3. For Redis, the command name is specified.

Downloading the Completed Extraction File

For short-term queries without additional filters, log extraction completes in a few minutes. For long-term logs or complex filtering conditions, extraction may take longer.

To download the completed log file:

1. From the list page: Click the Download button.

2. From the details page: Click a task to enter the details page, then click the Download button at the top right.

Encrypt Log File

The downloadable file is a '*.zip' file that compresses the '*.csv' file.

To set a password for this compressed file, ensure that the "Export a file with Encryption" option is set to "Required" in the 'General Settings > Company Management > Security' menu.

Log File Retention Policy

Extracted log files are retained for 30 days from the task creation date. Within this period, you can download the files as many times as needed. After 30 days, the files expire. If you need expired log files, create a new log extraction task.