Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.


Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.


Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.


Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » Featured, Networking

Tracking Network Connections Over Time

Submitted by on January 13, 2016 – 3:31 pm

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

If IPTables is used, open MySQL port to the relevant subnets
iptables -A INPUT -p tcp -m tcp -s --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` (
  `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`)

Step 2: download the script and add it to root cron
d=/var/adm/bin ; ; f=${d}/${fn} ; mkdir -p ${d} ; wget -q -O ${f} "${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

1 Step 3: Just for the hell of it, you can run the script manually in the “human” mode:
/var/adm/bin/ -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/ -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 '' AND remote_ip NOT LIKE '' );"

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 '' AND remote_ip NOT LIKE '' )
AND psef NOT REGEXP 'cups|bpbkar|ssh|none|gmond|salt'


Print Friendly, PDF & Email
  1. IMPORTANT: You must now edit /var/adm/bin/ to insert correct database server name and login credentials.

Leave a Reply