One Identity Safeguard for Privileged Sessions 6.9.5

NOTE: Creating report subchapters from search queries is currently an experimental feature of One Identity Safeguard for Privileged Sessions (SPS), therefore One Identity recommends that only administrators use this feature and only at their own risk.

You can turn any search query or statistics into a subchapter to add to your reports. This is an easy and flexible way of creating reports to monitor traffic, track certain parameters, or get alerted about particular events.

To create a search-based report subchapter from search results

Navigate to Search, and perform a query of your choice.
Click Search. Search results are displayed.
Click Create report.

The Create new subchapter page is displayed, with the Query field populated with your query.

If the Query field is not populated, enter a valid search query.

Figure 298: Search > Create report - Example subchapter created from search results with Query field populated
In the Name field, add a name to your report.
In Subchapter type, select the type that fits your query. You can choose from the following types:
- Sessions list: Displays a list of sessions.
  Set the number of sessions to show in the report as required.
- Timeline: Visualizes the distribution of sessions within a day/week/month, depending on the time range chosen for the report under Reporting > Configuration > Generate this report every > Day/Week/Month.
- Statistics: Visualizes the distribution of sessions based on the selected metadata.
  Select a Statistic presentation for your report, such as List, Pie chart, or Bar chart. Select the field (the metadata) to create your statistics on.
To add the subchapter to an existing report, click Add to a report and select from the list of available reports.

Alternatively, to configure a custom report from scratch, click Include in a new report. For more information, see Configuring custom reports.

Creating search-based report subchapters from scratch

The following describes how to create a search-based report subchapter from scratch.

To create a search-based report subchapter from scratch

If you have multiple SPS appliances and they are organized into a cluster where one of the nodes is the Search Master (or Central Search) node, log in to that node.
Navigate to Reporting > View & edit subchapters > Search-based.
Click Create new. The Create new subchapter page is displayed.
Figure 299: Reporting > View & edit subchapters > Search-based — Create new subchapter
In the Name field, add a name to your report.
In the Query field, type the query that you want to create a report from.
In Subchapter type, select the type that fits your query. You can choose from the following types:
- Sessions list: Displays a list of sessions.
  Set the number of sessions to show in the report as required.
- Timeline: Visualizes the distribution of sessions within a day/week/month, depending on the time range chosen for the report under Reporting > Configuration > Generate this report every > Day/Week/Month.
- Statistics: Visualizes the distribution of sessions based on the selected metadata.
  Select a Statistic presentation for your report, such as List, Pie chart, or Bar chart. Select the field (the metadata) to create your statistics on.
Click Save.
Click . Alternatively, navigate to Reporting > Create & Manage Reports.
Configure a custom report from scratch, or add the subchapter to an existing report. For more information, see Configuring custom reports.
When adding the subchapter you created, look for it under Reporting > View & edit subchapters > Search-based.

Creating statistics from custom database queries

The following describes how to create statistics from any custom queries from the One Identity Safeguard for Privileged Sessions (SPS) connection database. These custom statistics can be added to regular reports.

Caution:

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.

To create statistics from any custom queries from the SPS connection database

Navigate to the Reporting > View & edit subchapters > Advanced statistics page and click Create new.
Enter a name for the statistics. The created statistics will be available for reports under this name as a subchapter.

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.

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).

Caution:

Generating a report that includes an Advanced statistics chapter that returns several thousands of entries requires significant CPU and memory resources from One Identity Safeguard for Privileged Sessions (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.

Example

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

Example

select
  to_timestamp(audit_trail_downloads.download_time),
  audit_trail_downloads.username, 
  channels.channel_type,
  channels.connection,
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 329: Reporting > View & edit subchapters > Advanced statistics — Creating custom database queries

Select the type of chart to display, that is, Session list, Pie chart, or Bar chart.
- For the Session list, you can customize the name of the columns in the list by entering the name of the columns into the Column titles field.
- (Optional) 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 .
- For Bar chart, enter the name of the Y axis into the Y axis title field.
Add this new subchapter to a report.

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

For more information on how to add this subchapter to a selected report and assign privileges, see Configuring custom reports.

Database tables available for custom queries

This section describes the database tables, views, and functions of One Identity Safeguard for Privileged Sessions (SPS) that can be used in the custom queries of the Reporting > View & edit subchapters > Advanced statistics page. Generally, views contain a more organized dataset, while tables contain the raw data.

NOTE: The structure of these database tables may change in future One Identity Safeguard for Privileged Sessions (SPS) versions.

Table 10: 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 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.