Extract client IP Adresses from the listener.log file.

The listener log file is a simple text file, so searching for specific information inside is easy; however, in its raw
form, it’s difficult to extract collated information.

The simple and best way to do that is the widely used and humble linux commands: grep, awk, uniq, sort, wc etc.

If you are not sure, you can find the location of the listener log file by using the listener control utility:

[oracle@testoradb diag]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 19-JAN-2012 07:55:07

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 22-AUG-2011 01:03:21
Uptime 150 days 7 hr. 51 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/apps/oracle/testoradb/product/11.2.0/dbhome/network/admin/listener.ora
Listener Log File /u01/apps/oracle/testoradb/diag/tnslsnr/listener/alert/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testoradb)(PORT=1521)))
Services Summary...
Service "ORA_TEST" has 1 instance(s).
Instance "ORA_TEST", status READY, has 1 handler(s) for this service...
Service "testoraXDB" has 1 instance(s).
Instance "ORA_TEST", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@testoradb diag]$

Note the line that shows “Listener Log File,” which shows the directory of the listener log file.

[oracle@testoradb diag]$ cat listener.log | less
Mon Jul 18 02:56:26 2011
18-JUL-2011 02:56:26 * (CONNECT_DATA=(SERVICE_NAME=ORA_TEST)(CID=(PROGRAM=xHSSrv)(HOST=TEST_APP)(USER=apple))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.10)(PORT=44726)) *

establish * ORA_TEST * 0

The field Protocol Information has the following subfields:
PROTOCOL — the protocol that the client has used to connect, such as TCP.
HOST — the IP address of the client machine.
PORT — the port number established by the listener. (Note: It’s not the port number to which the listener is
listening, so this is not especially interesting to us.)

We can further narrow down on the IP addresses with the # of times they connected to the database on 18-Jan-2011 by:

[oracle@testoradb diag]$ cat listener.log | grep 18-JAN | grep CONNECT | awk -F* '{print $3}' | grep -o "192.*)" | grep -v 192.168.100.99 | awk -FPORT '{print $1}' | sort | uniq -c
5 192.168.100.32)(
255 192.168.100.33)(
19 192.168.100.34)(
60 192.168.100.56)(
11 192.168.100.58)(
1 192.168.100.62)(
6 192.168.100.71)(
9 192.168.100.163)(
1 192.168.100.164)(
12 192.168.100.165)(
5 192.168.100.166)(
2 192.168.100.167)(
2 192.168.100.169)(
[oracle@testoradb diag]$

It's rough but the info is here. After extracting the client IPs from the listener log file we then exclude the monitoring system's IP (192.168.100.99) from the list.

Cheers !
Harish.