Getting the Source IP/Hostname of a Query

Suresh Ramagiri -

If you're looking to determine the source IP/hostname of a particular query, you can query the "system.sessions" or "information_schema.processlist" tables.

If you know the exact or partial query, you can look for that in either table:

SQL> select source_ip, last_statement from system.sessions where last_statement 
LIKE 'select * from foo.bar';
+------------+-----------------------+
| source_ip | last_statement |
+------------+-----------------------+
| 10.1.2.185 | select * from foo.bar |
+------------+-----------------------+
SQL> select host, info from information_schema.processlist where info 
LIKE 'select * from foo.bar';
+------------------+-----------------------+
| host | info |
+------------------+-----------------------+
| 10.1.2.185:61684 | select * from foo.bar |
+------------------+-----------------------+

If you know the 'session_id' you can query for that directly:

SQL> select source_ip, last_statement from system.sessions where session_id=10198018;
+------------+-----------------------+
| source_ip | last_statement |
+------------+-----------------------+
| 10.1.2.185 | select * from foo.bar |
+------------+-----------------------+
SQL> select host, info from information_schema.processlist where id = 10198018;
+------------------+-----------------------+
| host | info |
+------------------+-----------------------+
| 10.1.2.185:61684 | select * from foo.bar |
+------------------+-----------------------+

With many external load balancers the methods above will show all queries as coming from the load balancer. If this is the case a good solution is to include the IP address for each query as a SQL comment.

Example:

SQL> select(1) /*client-IP: 192.168.1.1 */;

Adding the IP/hostname as a SQL comment will help identifying the source of a query in the process list and/or query log. It can also be helpful to add the line number in the application code that a particular query is run from. Both of these can help in identifying and resolving problem queries.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk