19.4. Procedure – Creating statistics from custom database queries

Purpose: 

To create statistics from any custom queries from the PSM connection database, complete the following steps. These custom statistics can be added to regular reports.

Warning

Hazard of denial of service (DoS)! This feature of PSM allows the user to execute read-only queries on the database of PSM. If the database is large (stores the data of many connections), and the query is not optimal, executing the query can consume significant CPU and memory resources, severely degrading the performance of PSM. Use this feature only if you possess the required knowledge about SQL queries.

Steps: 

  1. Navigate to the Reporting > Advanced statistics page and click .

  2. Enter a name for the statistics. The created statistics will be available for reports under this name as a subchapter.

  3. Enter the SQL query that returns the data you need into the Query field. Note the following important points:

    • The query must be a full PostgreSQL query.

    • SQL queries used for pie and bar charts must return a title and a cnt column, in this order. For example:

      select
        remote_username as title,
        count(*) as cnt
      from channels
      group by title
    • The query can be executed on the database tables and views that contain metadata about the audited connections, as well as the content of the audited connections (for example, the commands executed in a session) if indexing is used. Note that these tables do not contain any data from the upstream traffic, that is, passwords entered by the users are not available in the database.

    • The query must be limited to 5000 rows (LIMIT = 5000).

    • The structure of the accessible tables may change in future versions of PSM. For details about the tables and their contents, see Section 19.5, Database tables available for custom queries.

    The query can include the following macros: :range_start, :range_end. When including the statistics in a report, these macros will refer to the beginning and end dates of the reported interval. When clicking Preview, the macros will refer to the start and end of the current day.

    Example: 

    The following query generates a list of audit trail downloads within the reported interval (using standard date formatting), excluding administrator downloads:

    select
      to_timestamp(audit_trail_downloads.download_time),
      audit_trail_downloads.username,
      channels.channel_type,
      channels.connection,
      audit_trail_downloads.ip
    from audit_trail_downloads,
         channels
    where channels._connection_channel_id = audit_trail_downloads.id
    and audit_trail_downloads.download_time <= :range_start
    and audit_trail_downloads.download_time > :range_end
    and audit_trail_downloads.username != 'admin'
    order by audit_trail_downloads.download_time;

    Figure 19.6. Reporting > Advanced statistics — Creating custom database queries

    Reporting > Advanced statistics — Creating custom database queries
  4. Select the type of chart to display, that is, Bar, Pie or List.

    • For bar charts, enter the name of the Y axis into the Y axis title field.

    • For lists, you can customize the name of the columns in the list by clicking and entering the name of the column into the Column titles field.

  5. Click Preview to test the query.

  6. Optional step: By default, users of the search group can add these statistics to reports. To specify other groups, select Subchapter is accessible by the following groups and click .

    Note

    Accessing advanced statistics subchapters requires the Reporting > Advanced statistics privilege.

  7. Click to save the query.

  8. Add this new subchapter to a report. For details on how to add this subchapter to a selected report, see Procedure 19.2, Configuring custom reports