Chat now with support
Chat with Support

One Identity Safeguard for Privileged Sessions 5.9.0 - Administration Guide

Preface Introduction The concepts of SPS The Welcome Wizard and the first login Basic settings User management and access control Managing SPS
Controlling SPS: reboot, shutdown Managing Safeguard for Privileged Sessions clusters Managing a high availability SPS cluster Upgrading SPS Managing the SPS license Accessing the SPS console Sealed mode Out-of-band management of SPS Managing the certificates used on SPS
General connection settings HTTP-specific settings ICA-specific settings RDP-specific settings SSH-specific settings Telnet-specific settings VMware Horizon View connections VNC-specific settings Indexing audit trails Using the Search (classic) interface Using the Search interface Searching session data on a central node in a cluster Advanced authentication and authorization techniques Reports The SPS RPC API The SPS REST API SPS scenarios Troubleshooting SPS Configuring external devices Using SCP with agent-forwarding Security checklist for configuring SPS Jumplists for in-product help Third-party contributions About us

Creating statistics from custom database queries


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


Hazard of denial of service (DoS)! This feature of SPS allows the user to execute read-only queries on the database of SPS. 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 SPS. Use this feature only if you possess the required knowledge about SQL queries.

  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:

        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.

    • Limit the query to avoid unnecessarily long results, for example, LIMIT = 5000. Note that SPS automatically limits the results to 10000 entries (this is a hard limit, you cannot increase it).


      Generating a report that includes an Advanced statistics chapter that returns several thousands of entries requires significant CPU and memory resources from SPS. While generating such a partial report, the web interface of SPS can become slow or unresponsive.

    • The structure of the accessible tables may change in future versions of SPS. For details about the tables and their contents, see 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.


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

    from audit_trail_downloads,
    where channels._connection_channel_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 262: 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 .


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

  7. Click Commit 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 Configuring custom reports

Database tables available for custom queries

This section describes the database tables, views, and functions of SPS that can be used in the custom queries of the Reporting > Advanced statistics page. Generally, views contain a more organized dataset, while tables contain the raw data.


The structure of these database tables may change in future SPS versions.

Table 9: Database tables and views for custom queries
Database table Type Description
alerting table The list of alerting events. For details, see The alerting table.
aps table [OBSOLETE] The list of Audit Player indexing services that are available for SPS. For details, see The aps table.
archives table Data about the archiving processes. For details, see The archives table.
audit_trail_downloads table Data about the audit trail downloads. For details, see The audit_trail_downloads table.
channels table Contains metadata about the channel-opening requests and opened channels. This is the main table storing data about the connections. For details, see The channels table.
closed_connection_audit_channels view This view returns all audited channels whose connection have been closed. For details, see The closed_connection_audit_channels view.
closed_not_indexed_audit_channels view This view returns all audited channels whose connection have been closed, but have not been indexed yet. For details, see The closed_not_indexed_audit_channels view.
connection_events view List of commands or window titles detected in the connections. For details, see The connection_events view.
connection_occurrences view Contains the tokens that are used as search keywords in Content subchapter reports (reports from audit-trail content) and where these tokens appear in the audit trails. For details, see The connection_occurrences view.
connections view A view containing data of the connections. This data is identical to the information available on the Search > Search page. For details, see The connections view.
events table The commands or events extracted from the indexed audit trails. For details, see The events table.
file_xfer table Data about the files transfered in the audited connections (SCP, SFTP). For details, see The file_xfer table.
http_req_resp_pair table Information about the requests and responses in HTTP and HTTPS sessions. For details, see The http_req_resp_pair table.
indexer_jobs table Information and statistics about indexer jobs. For details, see The indexer_jobs table.
occurrences table Contains the tokens that are used as search keywords in Content subchapter reports (reports from audit-trail content) and where these tokens appear in the audit trails. For details, see The occurrences table.
progresses table [OBSOLETE] Which audit trail is assigned to which Audit Player for processing. For details, see The progresses table.
results table Contains the tokens that are used as search keywords in Content subchapter reports (reports from audit-trail content) and in which audit trails were these tokens found. For details, see The results table.
skipped_connections table List of errors encountered when processing audit trails. For details, see The skipped_connections table.
usermapped_channels view Information about sessions where usermapping was performed in the connection. For details, see The usermapped_channels view.

To search the content of audit trails that were processed using indexing, you can use the lucene SQL function. For details, see Querying trail content with the lucene-search function.

The alerting table


The structure of these database tables may change in future SPS versions.

Table 10: Columns of the alerting table
Column Type Description
alerting_time timestamp The timestamp of the alert.
alerting_type text The type of the alert.
channel_id integer This value is a reference to the ID of the channels table where the event occurred.
matched_content text The matched content.
matched_regexp text The matched regular expression.
rule_name text The name of the content policy rule.

The aps table


The structure of these database tables may change in future SPS versions.

This table contains information only about Audit Player indexers. It does not contain any information about the indexer service.

Table 11: Columns of the aps table
Column Type Description
ap_id integer [OBSOLETE] The ID of the Audit Player indexer service that is processing the audit trail.
dead boolean Set to 1 if the Audit Player indexer service on this host is considered to be unavailable.
id integer The unique ID number of the entry.
last_poll integer The timestamp of the last time when the Audit Player indexer service on this host requested an audit trail from SPS.
remote_addr text [OBSOLETE] The address of the host running the Audit Player indexer service.
Related Documents