There may be times when you want to extract all the client and host IP’s from the listener log to see who’s connecting to a particular database or service.
I was interested in a particular service so the following will extract all IP’s connecting in to that service. Please not that our listener logs are rotated and zipped.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
$ zgrep <SERVICE_NAME> *.zip | grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}' | sort | uniq -c | sort -n 1 10.30.37.199 1 10.20.67.111 3 10.30.66.154 3 10.30.76.38 3 10.20.117.171 4 10.20.112.58 10 10.20.203.9 16 10.20.112.112 18 10.20.117.186 30 10.20.193.17 32 10.30.35.91 39 10.20.193.18 44 10.20.112.23 48 10.20.193.15 53 10.20.193.16 70 10.20.112.6 74 10.20.107.143 100 10.40.32.23 101 10.20.203.8 703 10.20.193.13 989 10.20.193.78 995 10.20.193.79 14101 10.20.203.155 |
You could tweak it and pipe it to nslookup to get actual hostname but for my requirements the above was enough.