Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We are upgrading an old database server but want to make sure we update code on all other servers connecting to it. I'm thinking of mirroring the network port and logging what IP addresses connect to the server. I thought of turning on logging on the server itself, but I don't want to affect its performance since it is still in production.

Are there any recommendations on software I could use to log the IP addresses? It can be Windows, Linux, FreeBSD, VM, LiveCD, or something else. As long as it works on an x86-compatible box, I should be able to get it running.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

What OS is the database server itself running? If it's Linux, you could add a logging rule to the local firewall configuration with minimal impact on the server. Something along the lines of:

iptables -A INPUT -m state --state NEW -p tcp --dport <yourdbport> -j LOG --log-prefix "[database] "

You can add -m limit --limit 4/s to limit this rule to logging only 4 times/second (etc.), which if the connection rate is high will prevent it from hogging all the IO. This will log lines to syslog that look like:

[database] IN=eth0 OUT= MAC=00:16:3c:21:7c:f1:00:d0:00:6f:8c:00:08:00 SRC=a.b.c.d
  DST=w.x.y.z LEN=48 TOS=0x00 PREC=0x00 TTL=114 ID=16448 DF PROTO=TCP SPT=59871
  DPT=5900 WINDOW=8192 RES=0x00 SYN URGP=0 

The information you want is the SRC= field.

If you set up a separate Linux (or FreeBSD) system to receive traffic from a mirror port, you can use tcpdump to record the ip addresses. Something like:

tcpdump -w record_of_connections -i eth0 \
  dst port <yourdbport> and 'tcp[tcpflags] & tcp-syn != 0'

This will match all packets to port yourdbport that have the SYN flag set (e.g., they are a new connection attempt). After running this for a file, you can extract IP addresses like this:

tcpdump -r record_of_connections -n

Which will produce lines like this:

15:07:40.808035 IP 10.243.16.190.53967 > 10.243.18.22.http: Flags [S], 
  seq 1235802021, win 5840, options [mss 1380,sackOK,TS val 3739192250 
  ecr 0,nop,wscale 7], length 0

Where the fields are:

<time> <protocol> <src_addr>.<src_port> > <dest_addr>.<dest_port>: ...

Which means you can get just the source ip address like this:

tcpdump -r record_of_connections -n |
 awk '{print $3}' |
 sed 's/\.[^.]*$//' |
 sort -u

The sort -u gives you a unique list of addresses.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.