Overview

This article outlines the steps to obtain the list of client IP addresses that connect to a particular ScaleArc cluster.

Information

Query Logs capture all client connections (applications) and SQL statements run against any database server in the ScaleArc cluster. The query log is useful when you suspect an error from a client and want to know exactly what the client or application sent to the database server. 

The list of client IP address connecting to ScaleArc can be extracted from the Query Log debug logs by following these steps:

1. Ensure the logging level of the cluster is set to 'EXTENDED' (default setting) by clicking on 'Log Settings' in the cluster control panel.
2. Confirm that you have Query Logs for the particular period of interest by navigating to LIVE MONITOR > Logs > [Cluster Name] from the ScaleArc UI then select the 'Query Log' tab.
   
   
   Tip: The Query Logs can also be easily accessed through the cluster quick links located next to the cluster name on the ScaleArc Control Panel.
3. If the query logs contain log entries, it is possible to obtain the client IPs that initiated those queries by logging in to the ScaleArc instance via SSH, and going to the /data/logs/<date>/cid_<cluster_id>/ directory corresponding to the cluster-ID of your cluster. You can find the cluster-ID from the ScaleArc UI in the main Clusters page below the cluster name.
4. List down the query logs per hour as by running this command:
   

   # ls -lh idb.log*
   -rw-r--r-- 1 root root  5G Nov 18 15:59 idb.log.2.2019111815
   -rw-r--r-- 1 root root  6G Nov 18 16:59 idb.log.2.2019111816
   -rw-r--r-- 1 root root  5G Nov 18 17:59 idb.log.2.2019111817

5. We need to find out the format of the log entries before proceeding so list a few lines from the log files with

   tail -2 <name of a log file>

6. If the entries are in the format

   2021-02-08 09:02:05 #!#30#!#1#!#10.XX.XXX.XXX#!#SCALEARC#!#ISSP3#!#0#!#10.XXX.XXX.XXX#!#-1#!#-1#!#0#!#127301#!#20210208090204#!#ScaleArc: VERSION Request#!##!#1050137#!#184#!#0#!#0#!#0#!#19#!#0#!#0#!##!#231990061#!##!#-1#!#3#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!#0#!##!##!##!##!##!##!##!#0#!#-1#!##!#-1#!#0#!#0#!#0#!#0#!#110#!#-1

   then you can obtain the unique client IP addresses from the log files by running the following command:
   

   # cat idb.log.* | awk -F  '#!#' '{print $4}' | sort -u

   The result will be a list of IP addresses that connected to ScaleArc at the given time. Below is a sample output from running the command:
   

   192.168.0.68
   192.168.1.180
   192.168.8.77
   192.168.9.248
   127.0.0.1

7. Whereas, if the entries are in the following text format

   2021-02-08 09:02:18 CID: 1, Client IP: 1XX.XXX.XXX.XXX, User: unknown, Debug_Code: 0, Message: New client connection received from Client (1XX.XXX.XXX.XXXX:60473) on interface (10.XXX.XXX.XXX:1521) (fd:41), State: 1, SSID: 1050138, DB: ISSP3, DB IP: NONE, Type: 37

   then you can obtain the unique client IP addresses from the log files by running the following command:
   

   # cat idb.log.* | awk -F  ',' '{print $2}' | grep Client | sort -u

   The result will be a list of IP addresses that connected to ScaleArc at the given time. Below is a sample output from running the applicable command shown above:
   

   Client IP: 192.168.0.68
   Client IP: 192.168.1.180
   Client IP: 192.168.8.77
   Client IP: 192.168.9.248
   Client IP: 127.0.0.1

Back to top