Martins Blog

Trying to explain complex things in simple terms

Check for non-successful connection attempts in listener.log

Posted by Martin Bach on February 9, 2010

This could become a regular question from your security team-can you find out if someone tried to mess with the listener when trying to connect? Often you see hackers target port 1521 and sending random data garbage through the wire. The listener initially accepts the connection but closes it when it doesn’t receive data it expects.

This is another reason why unix/linux is way cooler than Windows.

Let’s assume you need to find if there were any unsuccessful connection entries in the listener.log for a given day. First of all-how do they have to look if they are successful? Typical entries are as follows:

1 08-FEB-2010 04:49:54 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=testserv) *
   (ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=4307)) * establish * testserv * 0
2 08-FEB-2010 04:49:55 * service_update * dev1 * 0
3 08-FEB-2010 04:49:57 * service_update * dev3 * 0

The important bit is at the end-the "0" means "normal, successful completion". If there is a problem, you would therefore assume there is an Oracle error number from the TNS range (>12000).

Awk is the swiss army knife to find such results, and this is how you could use it (apologies in advance for my poor command of awk-if you know a better way please let me know!)

$> grep "08-FEB" listener.log | awk  '{ if ( $NF != 0 ) print $0 }'

The built-in variable NF is the last of all the fields which are enumerated from $1. So in summary, this little snippet does the following:

  1. Find all lines for a given day (here: February 8th) in the $ORACLE_HOME/network/log/listener.log file
  2. Print the lines where the last field is not equal to 0

This can easily be wrapped up into a nagios check to be executed by NRPE-if in case of doubt: simplify (to quote Piet de Visser).

So next time you get output such as:

1 09-FEB-2010 16:25:56 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.36.135)
   (PORT=3929)) * establish * <unknown sid> * 12525

... something fishy might be going on. oerr ora <number> gives you more information about what happened.

By the way this is gawk-3.1.5-14.el5 on RHEL 5.3.

About these ads

5 Responses to “Check for non-successful connection attempts in listener.log”

  1. Don Seiler said

    Great article! One small correction:

    Find all lines for a given day (here: February 8th) in the $ORACLE_HOME/network/log/listener.ora file

    I think you mean listener.log, not listener.ora. ;)

  2. [...] Martin Bach-Check for non-successful connection attempts in listener.log [...]

  3. helfgott said

    Pretty cool, i like that! :) i’ve modified it adding grep -i ´date +(%d-%b´) so it checks everyday.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,399 other followers

%d bloggers like this: