19.5.10. 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 the connections view has the following columns. For details of the different columns, see Section 16.1.5, Connection metadata.

Note

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

ColumnTypeDescription
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 PSM 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 Procedure 18.1, 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.

Table 19.9. Columns of the connections view

Previous  Up  Next
19.5.9. The connection_occurrences view  Home  19.5.11. The events table