Originally published January 7, 2018 @ 3:03 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 /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} "http://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
/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 -pStep 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();
Experienced Unix/Linux System Administrator with 20-year background in Systems Analysis, Problem Resolution and Engineering Application Support in a large distributed Unix and Windows server environment. Strong problem determination skills. Good knowledge of networking, remote diagnostic techniques, firewalls and network security. Extensive experience with engineering application and database servers, high-availability systems, high-performance computing clusters, and process automation.