Reports

The current Reports feature is provided as a Beta version in version 10.2.0.

Overview

QueryPie Reports is a feature that outputs data required for audit responses in the form of reports.

It can output details such as user and administrator status and history, security settings, user-specific permission statuses and histories for databases/servers, as well as database/server access and query/command usage history.

Viewing Report List

In the Audit menu, navigate to the Reports > Reports to view the list of report generation tasks.

To access the Reports menu, your account must be granted Report Audit Full Access administrator permissions.

Creating a Report

1. Search: You can search using the Task Name.

2. Create Task: Navigate to the report creation page.

3. Refresh: Reload the task list.

4. Report Section: Displays the report area specified during task creation. The three sections include:

   1. QueryPie System

   2. QueryPie DAC

   3. QueryPie SAC

5. Task Name: The name of the task entered during report creation.

6. Scheduling: The recurrence frequency specified during task creation, with the following options:

   1. None: No recurrence (one-time generation).

   2. Daily: Repeats daily.

   3. Weekly: Repeats weekly.

   4. Monthly: Repeats monthly.

   5. Quarterly: Repeats quarterly.

7. Created By: The user who created the report task.

8. Created At: The timestamp of when the report task was created.

9. Last Task At: The timestamp of the most recent report generation (empty if no reports have been generated).

10. Last Task Status: The status of the most recent report generation task, which can be:

    1. Succeeded: Successful

    2. In Progress: Ongoing

    3. Failed: Failed

11. Last Report File: A button to download the most recently generated report file.

    1. If no file exists or the file has expired, the button will be disabled.

    2. Password settings for file downloads can be configured in Security Settings.

    3. When password protection is enabled, you must set a password during download. This password is required to extract the downloaded file.

Viewing Report Details

Click on the desired report from the list to open a drawer displaying detailed information.

1. Unschedule Task: For recurring reports, you can unschedule the task to stop further automatic generation.

2. Report Section: Display the report area (e.g., QueryPie General, DAC, SAC)

   1. Click the magnifying glass icon to view detailed information for the selected section.

3. Scheduling: Show the recurrence frequency (e.g., None, Daily, Weekly, Quarterly)

4. Date Range: For one-time reports, displays the date range of the report.

5. Start Date: Indicate the start date for recurring reports.

6. Next Task At: Show the scheduled time for the next task (left empty for one-time reports)

7. User Scope: Display the scope of users included in the report

8. Resource Scope: Display the scope of resources included in the report.

9. Report List Tab: Display a list of generated reports

   1. Report No.: A serial number assigned to each report within the task.

   2. Created At: The timestamp indicating when the report was generated.

   3. Expired At: The date and time when the report file will expire (or is set to expire).

   4. Status: The current status of the report task (either Succeeded or Failed)

   5. Report File: A link to download the report file.

      • If the generated file is unavailable or has already expired, the link will be disabled.

      • You can specify whether a password is required for the file download in the Security settings.

      • When password protection is enabled, a password will be set during the file download. The password will then be required to unzip the file later.

10. Action History Tab: Display the user's action history for the report.

    • Action At: Date and time the action was performed.

    • Action Type: Type of action (either Report Download or Report Unschedule).

    • Target Report No.: The target report serial number (left blank if unavailable).

    • Action By: The person who performed the action.

Creating Report

You can access the report creation page by clicking the + Create Task button on the report list screen.

Providing Task Basic Information

1. Task Name: Provide a name to identify the report creation task.

2. Scheduling: Set the recurrence frequency for the report creation task.

   1. None: Select this option for a one-time task without repetition.

   2. Daily: Select this option for the task to repeat daily.

   3. Weekly: Select this option for the task to repeat weekly.

   4. Monthly: Select this option for the task to repeat monthly.

   5. Quarterly: Select this option for the task to repeat quarterly.

3. Date Range: Specifies the period range for historical data.

   1. If Scheduling is set to None, specify the output range for historical data.

   2. Current data will be output based on the task execution time, regardless of the Date Range input.

4. Start Date: Start date for recurring tasks.

   1. If Scheduling is set to Daily, Weekly, Monthly, or Quarterly, specify the start date for the task.

Configuring Report Output Items

On the left side of the creation page, configure the report output items, scope, and filters.

1. Report Section: Choose the report section based on the service (QueryPie System, DAC, or SAC).

2. Scope : Define the scope of the report. Refer to the (Optional) Specify Report Scope section below for detailed guidance.

3. Raw Data: Specify whether to include raw data in the report output:

   1. When enabled, raw data is provided as a CSV file along with the report.

   2. When disabled, only aggregated data is provided in HTML format.

4. Select All: Toggle to select or deselect all report output items.

For available output items by report section, and details on scope and filter support for each item, see the sections below.

(Optional) Specifying Report Scope

The scope specification feature is used to generate reports based on specific users or databases/servers.

1. To view the list of permissions granted to Infrastructure Security Team members across all databases registered in QueryPie:

   1. Set the User Scope to Infrastructure Security Team members.

2. To view the permissions and grant history for all users on servers assigned to a specific service:

   1. Set the Resource Scope to a tag that identifies the servers assigned to the specific service.

You can specify the report scope by clicking the Scope button to the right of the Report Section.

When applying a scope, report output items that cannot support the specified scope will be disabled.

Example:
If the QueryPie SAC section is selected and both User Scope and Resource Scope are specified, the following items will be disabled:

• Command Template Status

• Command Template Management History

• User Scope: Specify the User-Based Scope

  • Define the report output scope based on users. When entering a group, the search will include users within the specified group.

  • Clicking the dropdown displays the list of groups and users currently registered in QueryPie. Select the desired group or user by clicking on it.

  • You can also search for groups or users by typing their names in the input field.

  • If multiple values are selected, they will be combined using an OR condition, broadening the scope.

• Resource Scope (Tags): Specify the Resource-Based Scope

  • Define the report output scope based on tagged databases or servers.

  • To add a tag, place the cursor in the Input Tag field, type the key, press Enter, type the value, and press Enter again to complete the tag entry.

  • However, if the Section is set to QueryPie System, resource scope configuration is not supported.

After completing your input, click the Apply button to save the scope and close the modal. To preview the applied settings, click the Load Preview button.

(Optional) Configuring Detailed Filters for Report Output Items

For some report output items, you can configure additional detailed filters.

• button is displayed to the left of item names that support detailed filters.

  • Clicking the button opens a modal to input filter criteria, and clicking Applybutton saves your settings.

  • Use the Load Preview button to view the applied settings.

• When a detailed filter is active, the button changes to indicating its configured status.

In version 10.2.0, the following detailed filters are supported:

• QueryPie System - User Account Status

  • Filter user accounts based on their status:

  • Active, Locked, Expired, Locked Manually

• QueryPie DAC - DB Access History

  • Time range filtering for the Action At column:

    • Input start and end times.

    • Choose whether to include or exclude the time range.

      • If Exclude is selected, only records outside the specified time range will be displayed.

• QueryPie SAC - Server Access History

  • Time range filtering for the Action At column:

    • Input start and end times.

    • Choose whether to include or exclude the time range.

      • If Exclude is selected, only records outside the specified time range will be displayed.

• QueryPie SAC - Command History, Restricted Commands

  • Keyword filtering for the Command column:

    • Input keywords for exact matches.

    • When multiple keywords are entered, they are combined with an OR condition, broadening the search scope.

Report Preview

The report preview allows you to check how the report will be generated based on the current input.

• Aggregation details are not reflected in the preview and are represented as {n}.

• Raw data shows up to the five most recent records as of the current moment, with any additional entries omitted.

• In version 10.2.0, tasks targeting future time periods may not display accurate durations in the report preview.

If the report's schedule, date range, scope, output items, or detailed filters are modified, you must reload the preview.

Saving the Report

After running the report preview, click the Save button to save the report task.

As of version 10.2.0, reports can only be generated in English.

Reference: Supported Scope and Detailed Filters for Report Output Items