Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Simple Lua script to consume the conntrack table from luci and persist it in a MySQL database
#!/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()
@sigmaprojects

This comment has been minimized.

Copy link
Owner Author

@sigmaprojects sigmaprojects commented Dec 7, 2014

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.