sp_iqclient_lookup procedure

Allows a client application to determine the Sybase IQ user account responsible for a particular data stream, as observed in a network analyzer originating from a specific client IP address/port.

sp_iqclient_lookup [ 'IPaddress' ], [ Port ], [ UserID ]

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

The sp_iqclient_lookup procedure takes the client IP address and port number and returns a single row containing Number (the connection ID), IPaddress, Port, and UserID.

1> sp_iqclient_lookup '158.76.235.71',3360
2> go

Number   IPaddress      Port    UserID
------   ---------      ----    ------
15       158.76.235.71  3360    rdeniro

Optionally, you can pass a third argument to select only the UserID. If no arguments are passed, sp_iqclient_lookup returns all current logins with their IP addresses and port numbers. For example:

sp_iqclient_lookup

Number   IPaddress        Port    UserID
------   ---------        ----    ------
11       162.66.131.36    2082    mbrando
21       162.66.100.233   1863    apacino
22       162.66.100.206   8080    jcaan
23       162.66.100.119   6901    rduvall
24       162.66.100.125   7001    dkeaton
25       162.66.100.124   6347    jcazale

(6 rows affected)
(return status = 0)

If a client application is not using TCP/IP or for internal connections, the address appears as 127.0.0.1.

The sp_iqclient_lookup stored procedure may impact server performance, which varies from one installation to another. Finding the login name entails scanning through all current active connections on the server; therefore, the impact may be greater on servers with large numbers of connections. Furthermore, this information cannot be cached as it is dynamic — sometimes highly dynamic. It is, therefore, a matter for the local system administrator to manage the use of this stored procedure, as well as monitor the effects on the server, just as for any other client application that uses server facilities.

Shows IP addresses for UserID jcazale:

sp_iqclient_lookup null, null, jcazale

Number   IPaddress        Port    UserID
------   ----------       ----    ------

11       162.66.131.36    2082    jcazale
15       164.66.131.36    1078    jcazale

Shows IP addresses from client IP 162.66.131.36:

sp_iqclient_lookup '162.66.131.36'

Number   IPaddress        Port    UserID
------   ----------       ----    ------
11       162.66.131.36    2082    jcazale
12       162.66.131.36    1078    jcaan