19.5. Database tables available for custom queries

This section describes the database tables, views, and functions of PSM 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.

Note

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

Database tableTypeDescription
alerting table The list of alerting events. For details, see Section 19.5.1, The alerting table.
aps table The list of Audit Player indexing services that are available for PSM. For details, see Section 19.5.2, The aps table.
archives table Data about the archiving processes. For details, see Section 19.5.3, The archives table.
audit_trail_downloads table Data about the audit trail downloads. For details, see Section 19.5.4, 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 Section 19.5.5, The channels table.
closed_connection_audit_channels view This view returns all audited channels whose connection have been closed. For details, see Section 19.5.6, 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 Section 19.5.7, The closed_not_indexed_audit_channels view.
connection_events view List of commands or window titles detected in the connections. For details, see Section 19.5.8, 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 Section 19.5.9, 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 Section 19.5.10, The connections view.
events table The commands or events extracted from the indexed audit trails. For details, see Section 19.5.11, The events table.
file_xfer table Data about the files transfered in the audited connections (SCP, SFTP). For details, see Section 19.5.12, The file_xfer table.
http_req_resp_pair table Information about the requests and responses in HTTP and HTTPS sessions. For details, see Section 19.5.13, The http_req_resp_pair table.
indexer_jobs table Information and statistics about indexer jobs. For details, see Section 19.5.14, 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 Section 19.5.15, The occurrences table.
progresses table Which audit trail is assigned to which Audit Player for processing. For details, see Section 19.5.16, 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 Section 19.5.17, The results table.
skipped_connections table List of errors encountered when processing audit trails. For details, see Section 19.5.18, The skipped_connections table.
usermapped_channels view Information about sessions where usermapping was performed in the connection. For details, see Section 19.5.19, The usermapped_channels view.

Table 19.1. Database tables and views for custom queries

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