The closed_not_indexed_audit_channels view
NOTE: The structure of these database tables may change in future One Identity Safeguard for Privileged Sessions (SPS) versions.
This view returns all audited channels whose connection have been closed, but have not been indexed yet. The view is defined as follows:
create view closed_not_indexed_audit_channels
as
select *
from channels
where audit is not null
and (index_status = 1
or index_status = 2);
For details on the returned columns, see The channels table.
The connection_events view
NOTE: The structure of these database tables may change in future One Identity Safeguard for Privileged Sessions (SPS) versions.
For terminal connections, this view collects the commands issued in a connection. For graphical connections, this view collects the window titles detected in the connection. The view is defined as follows:
select
channels._connection_channel_id as id,
events.event,
events.printable
from channels,
events
where channels.id = events.channel_id;
Querying the table (for example, select * from connection_events limit 10;) will return results similar to the following:
id | event | printable
----+-------------------------------------------------------------+-----------
1 | [user@exampleserver ~]$ ls | t
1 | [user@exampleserver ~]$ exit | t
2 | [user@exampleserver ~]$ su - | t
2 | Password: | t
2 | [root@exampleserver ~]# | t
2 | [root@exampleserver ~]# ifconfig | t
2 | [root@exampleserver ~]# ifconfig | t
2 | [root@exampleserver ~]# ifconfig | t
4 | [user@exampleserver ~]$ | t
4 | [user@exampleserver ~]$ | t
The connection_events view has the following columns.
Table 18: Columns of the connection_events table
event |
text |
The command executed, or the window title detected in the channel (for example, ls, exit, or Firefox). |
id |
integer |
The unique ID number of the entry. |
printable |
boolean |
Set to 1 if every character of the command can be displayed. |
The connection_occurrences view
The view is defined as follows:
select
channels._connection_channel_id as id,
results.token,
occurrences.start_time,
occurrences.end_time,
occurrences.screenshot
from channels,
results,
occurrences
where channels.id = results.channel_id
and results.id = occurrences.result_id;
NOTE: The structure of these database tables may change in future One Identity Safeguard for Privileged Sessions (SPS) versions.
Table 19: Columns of the connection_occurrences table
end_time |
integer |
End time: Date when the channel was closed. |
id |
text |
The unique id of the entry. |
screenshot |
text |
The filename of the PNG screenshot (as stored on SPS) about the occurrence of the search token. |
start_time |
integer |
Start time: Date when the channel was started. |
token |
text |
The search token visible on the screenshot. |
The connections view
This view collects the metadata of the connections. The view is defined as follows:
select
channels."connection",
channels.protocol,
channels._connection_channel_id as id,
channels.connection_id,
min(channels.session_start) as session_start,
max(channels.session_end) as session_end,
max(channels.src_ip) as src_ip,
max(channels.src_port) as src_port,
max(channels.server_ip) as server_ip,
max(channels.server_port) as server_port,
max(channels.username) as username,
max(channels.remote_username) as remote_username,
max(channels.channel_policy) as channel_policy,
sum(case
when channels.session_end is null then 1
else 0
end) as active
from channels
group by channels._connection_channel_id,
channels.protocol,
channels."connection",
channels.connection_id;
Querying the table (for example, select * from connections limit 10;) will return results similar to the following:
connection | protocol | id | connection_id | session_start | session_end | src_ip | src_port | server_ip | server_port | username | remote_username | channel_policy | active
-------------+----------+--------+-------------------------+---------------+-------------+---------------+----------+-------------+-------------+-----------+-----------------+----------------+--------
SSH_Access2 | ssh | 1 | 5516465814bc36d5570ec8 | 1271098736 | 1271099582 | 192.168.0.62 | 4312 | 192.168.0.20 | 22 | joe | joe | shell-only | 0
SSH_Access | ssh | 10 | 20790868454bc33027964a0 | 1271258787 | 1271259645 | 10.100.58.27 | 2298 | 192.168.0.20 | 22 | joe | joe | shell-only | 0
SSH_Access | ssh | 100 | 20790868454bc33027964a0 | 1272391671 | 1272396886 | 10.100.58.14 | 51342 | 192.168.0.20 | 22 | phil | phil | shell-only | 0
SSH_Access | ssh | 1000 | 20790868454bc33027964a0 | 1274450541 | 1274475742 | 10.100.56.14 | 4633 | 192.168.0.20 | 22 | rick | rick | all | 0
SSH_Access2 | ssh | 10000 | 5516465814bc36d5570ec8 | 1282753195 | 1282764804 | 192.168.40.34 | 53097 | 192.168.0.20 | 22 | vivian | vivian | shell-only | 0
SSH_Access2 | ssh | 100000 | 5516465814bc36d5570ec8 | 1314979916 | 1314986038 | 192.168.40.85 | 34743 | 192.168.0.20 | 22 | elliot | elliot | Shell-SCP | 0
SSH_Access2 | ssh | 100001 | 5516465814bc36d5570ec8 | 1314979917 | 1314984561 | 192.168.40.65 | 56405 | 192.168.0.20 | 22 | root | root | Shell-SCP | 0
SSH_Access2 | ssh | 100002 | 5516465814bc36d5570ec8 | 1314979940 | 1314984171 | 192.168.40.100 | 1082 | 192.168.0.20 | 22 | allen | allen | Shell-SCP | 0
SSH_Access2 | ssh | 100003 | 5516465814bc36d5570ec8 | 1314979955 | 1314981233 | 192.168.40.10 | 34263 | 192.168.0.20 | 22 | steve | steve | Shell-SCP | 0
SSH_Access2 | ssh | 100004 | 5516465814bc36d5570ec8 | 1314980025 | 1314991838 | 192.168.40.33 | 58500 | 192.168.0.20 | 22 | clark | clark | Shell-SCP | 0
(10 rows)
The connections view has the following columns.
NOTE: The structure of these database tables may change in future One Identity Safeguard for Privileged Sessions (SPS) versions.
Table 20: Columns of the connections view
active |
bigint |
|
channel_policy |
text |
The name of the Channel policy that applied to the particular channel of the connection. |
connection |
text |
The name of the Connection Policy, as configured on the SPS web interface. |
connection_id |
text |
The unique ID of the TCP connection. |
id |
text |
The ID of the channel within the connection. |
protocol |
text |
Protocol: The protocol used in the connection (Citrix ICA, HTTP, RDP, SSH, Telnet, or VNC). |
remote_username |
text |
Username on server: The username used to log in to the remote server. This username can differ from the client-side username if usermapping is used in the connection. For details on usermapping, see Configuring usermapping policies. |
session_end |
integer |
End time: Date when the channel was closed. |
session_start |
integer |
Start time: Date when the channel was started. |
src_ip |
text |
Source IP: The IP address of the client. |
src_port |
integer |
Source port: The port number of the client. |
username |
text |
Username: The username used in the session.
-
If the user performed inband gateway authentication in the connection, the field contains the username from the gateway authentication (gateway username).
-
Otherwise, the field contains the username used on the remote server. |