Oracle Database 19c -- Parse Listener Log to Identify Connected Clients

Problem

You need to identify all connected clients to a database for a specific interval of time.

Audit was not previously enabled for successful logons

Solution

You may parse the listener log file to extract all previously connected clients

1) Identify the listener log location using: 

$ LISTENER_LOG=$(lsnrctl status | grep "Listener Log File" | sed 's|alert/log.xml|trace/listener.log|' | awk '{print $4}')

$ echo $LISTENER_LOG

2) Visually check the log file to determine the timestamp format, different releases had different timestamp formats

3) Extract the range of records of interest for faster processing:

$ awk '/14-DEC-2023/,/non_matching_string/ {print $0}' $LISTENER_LOG > /tmp/listener_part.log

The above command will extract all records starting from 14-DEC-2023 till the end of the file. Replace non_matching_string with a non-inclusive timestamp to limit the range from the end

4) Extract IP addresses of the previously connected clients:

$ grep -P -o "\(HOST=\d+\.\d+\.\d+\.\d+\)\(PORT=\d+\)" /tmp/listener_part.log | grep -P -o "\(HOST=\d+\.\d+\.\d+\.\d+\)"| grep -P -o "\d+\.\d+\.\d+\.\d+" | sort -u > /tmp/ips.txt

/tmp/ips.txt will contain a list of IP addresses

5) Optionally generate hostnames for the IP addresses extracted in previous steps:

$ grep -P -o "\(HOST=\d+\.\d+\.\d+\.\d+\)\(PORT=\d+\)" /tmp/listener_part.log | grep -P -o "\(HOST=\d+\.\d+\.\d+\.\d+\)"| grep -P -o "\d+\.\d+\.\d+\.\d+" | sort -u | xargs -I {} host {} | awk '{print $5}' | grep . > /tmp/hosts.txt

6) If hostnames generated, present the both lists as single view:

$ paste /tmp/ips.txt /tmp/hosts.txt