Firewall changes, datacenter migrations, application re-hostings, server decommissions are just some of the activities where having a record of network connections over time can help avoid confusion and unplanned downtime. To capture all network connections, you would need to run tcpdump. However, this approach requires lots of local disk space and usually you would have to limit your data collection run down to a few minutes.

What you really want is to keep track of persistent or frequent network connections and for this purpose netstat is the best tool. The setup below is fairly straight-forward: a local script, a cron job, MySQL client connecting to remote MySQL server and adding comma-separated data to a table. To quote Felonious Gru, “Now, the rest of the plan is simple: I fly to the moon. I shrink the moon. I grab the moon. I sit on the toilet.”

Step 1: set up database and table on your MySQL server that will serve as the central collection point for all your network connection data.

Install MySQL server software

yum -y install mysql-server
/sbin/service mysqld start
/sbin/chkconfig mysqld on
/usr/bin/mysql_secure_installation

If IPTables is used, open MySQL port to the relevant subnets

iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT

Create database

mysql -uroot -ppassword
mysql> CREATE DATABASE sysinfo ;
mysql> GRANT ALL PRIVILEGES ON sysinfo.* to root@'%' IDENTIFIED BY 'password' WITH GRANT OPTION ;
mysql> quit

Create table

mysql -uroot -ppassword
mysql> CREATE TABLE `netstat_port_tracker` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time_db` datetime DEFAULT NULL,
  `time_epoch` int(11) DEFAULT NULL,
  `host_name` varchar(45) DEFAULT NULL,
  `host_id` varchar(45) DEFAULT NULL,
  `host_ip` varchar(45) DEFAULT NULL,
  `protocol` varchar(45) DEFAULT NULL,
  `local_ip` varchar(45) DEFAULT NULL,
  `local_port` int(11) DEFAULT NULL,
  `remote_ip` varchar(45) DEFAULT NULL,
  `remote_port` int(11) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `exe` longtext,
  `psef` longtext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=27167 DEFAULT CHARSET=latin1 ;

Step 2: download the script and add it to root cron

d=/var/adm/bin ; fn=netstat_port_tracker_v2.sh ; f=${d}/${fn} ; mkdir -p ${d} ; wget -q -O ${f} "https://www.krazyworks.com/wp-content/uploads/scripts/${fn}" ; chmod 755 ${f} ; yum -y install mysql >/dev/null 2>&1 ; c=/var/spool/cron/root ; if [ `grep -c ${fn} ${c}` -eq 0 ] ; then echo "*/15 * * * * ${f} -d >/dev/null 2>&1" >> $${c} ; /sbin/service crond reload ; fi ; ${f} -d

¹ Step 3: Just for the hell of it, you can run the script manually in the “human” mode:

/var/adm/bin/netstat_port_tracker_v2.sh -h

You can also just get a list of all ports that are currently in use. This can be useful when doing an initial config of firewall.

/var/adm/bin/netstat_port_tracker_v2.sh -p

Step 4: After the cron job runs, you can connect to the database and view the fruits of your labor:

db_host="servername" ; db_user="username" ; db_pass="password" ; db_name="sysinfo" ; tbl_name="netstat_port_tracker" ; MYSQL="/usr/bin/mysql --batch --skip-column-names --max_allowed_packet=100M -h${db_host} -u${db_user} -p${db_pass} ${db_name} -e" ; ${MYSQL} "SELECT DISTINCT protocol,local_ip,local_port,remote_ip,remote_port,status,exe,psef FROM sysinfo.netstat_port_tracker WHERE host_name LIKE '`hostname`' AND time_db >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND ( remote_ip NOT LIKE '0.0.0.0' AND remote_ip NOT LIKE '127.0.0.1' );"

Here’s another example of a more useful query that excludes some common system processes, allowing to identify remote connections established by user applications:

SELECT DISTINCT local_ip, local_port, remote_ip, remote_port, status, exe, psef
FROM sysinfo.netstat_port_tracker
WHERE host_name LIKE '%hostname%'
AND ( remote_ip NOT LIKE '0.0.0.0' AND remote_ip NOT LIKE '127.0.0.1' )
AND psef NOT REGEXP 'cups|bpbkar|ssh|none|gmond|salt'
AND time_db BETWEEN NOW() - INTERVAL 7 DAY AND NOW();