Simple Lua script to consume the conntrack table from luci and persist it in a MySQL database

saveconntrack.lua

#!/usr/bin/lua
--[[
	Author:	Don Quist
	Date:	12/06/14
	https://www.sigmaprojects.org/
	https://gist.github.com/sigmaprojects
	Simple Lua script to consume the conntrack table from luci and persist it in a MySQL database
--]]

function getDBConnection()
	require "luasql.mysql"
	local env = luasql.mysql()
	local con = env:connect("networktracker","networktracker","gistdemo","seshat","3306")
	return con
end

function main()
	local sys = require "luci.sys"
	local conntrack = sys.net.conntrack()
	local con = getDBConnection()
	local statement = ""
	for i, p in pairs (conntrack) do
		-- if a row in conntrack contains 'unknown' it won't have a dport or sport, so change up the statement to prevent string.format from exploding
		-- because of how the table structure is composed with primary keys on dport,dst,layer3,layer4,sport, and src; we simply update the packet and bytes count 
		if p.layer4 == "unknown" then
			statement = string.format( "INSERT INTO conntrack (bytes,dst,layer3,layer4,packets,src) VALUES ('%s',INET_ATON('%s'),'%s','%s','%s',INET_ATON('%s')) ON DUPLICATE KEY UPDATE bytes=VALUES(bytes),packets=VALUES(packets);" , p.bytes, p.dst, p.layer3, p.layer4, p.packets, p.src)
		else
			statement = string.format( "INSERT INTO conntrack (bytes,dport,dst,layer3,layer4,packets,sport,src) VALUES ('%s','%s',INET_ATON('%s'),'%s','%s','%s','%s',INET_ATON('%s')) ON DUPLICATE KEY UPDATE bytes=VALUES(bytes),packets=VALUES(packets);" , p.bytes, p.dport, p.dst, p.layer3, p.layer4, p.packets, p.sport, p.src)
		end
		local res = con:execute(statement)
		print(res)
	end
	
	con:close()
end

function setup()
	local con = getDBConnection()
	local statement = [[
		CREATE TABLE `conntrack` (
			`bytes` int(11) DEFAULT NULL,
			`dport` int(11) NOT NULL DEFAULT '0',
			`dst` int(10) unsigned NOT NULL DEFAULT '0',
			`layer3` enum('ipv4','ipv6') NOT NULL DEFAULT 'ipv4',
			`layer4` enum('udp','tcp','unknown') NOT NULL DEFAULT 'udp',
			`packets` int(11) DEFAULT NULL,
			`sport` int(11) NOT NULL DEFAULT '0',
			`src` int(10) unsigned NOT NULL DEFAULT '0',
			`created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
			`updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (`dport`,`dst`,`layer3`,`layer4`,`sport`,`src`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	]]
	print("Creating conntrack table")
	con:execute(statement)
	con:close()
end

--setup()
main()

I needed a way to persist the conntrack table on an OpenWRT based router.

This uses the included luci.sys.net library to get the conntrack and LuaSQL (MySQL in this instance) to persist.

The table structure has primary keys on nearly all the fields, except namely packets and bytes. If a duplicate key is detected on INSERT, it'll update those 2 fields instead.

Figured I'd put this on Gist in case anyone else needed it.
Never made anything in Lua before, so I'm sure there are better ways to do certain things.