pvl.verkko: re-add last_msg back to db, implement state, seen filtering in web frontend
--- a/pvl/verkko/db.py Fri Oct 12 16:34:53 2012 +0300
+++ b/pvl/verkko/db.py Thu Oct 18 21:16:26 2012 +0300
@@ -8,17 +8,25 @@
# TODO: count, completely separate dhcp_events?
dhcp_hosts = Table('dhcp_hosts', metadata,
- Column('rowid', Integer, primary_key=True),
+ # TODO: rename: id
+ Column('rowid', Integer, primary_key=True),
# unique
- Column('ip', String),
- Column('mac', String),
- Column('gw', String),
+ Column('ip', String, nullable=False),
+ Column('mac', String, nullable=False),
+ Column('gw', String, nullable=False),
# updated
- Column('name', String),
- Column('first_seen', DateTime),
- Column('last_seen', DateTime),
+ Column('first_seen', DateTime, nullable=False),
+ Column('last_seen', DateTime, nullable=False),
+
+ # TODO: rename: state
+ Column('last_msg', String, nullable=False),
+
+ # scalar; updated
+ Column('name', String, nullable=True),
+
+ UniqueConstraint('ip', 'mac', 'gw'),
)
# for ORM models
@@ -50,7 +58,7 @@
# SQL
def select (self, query) :
- return self.engine.connect().execute(query)
+ return self.engine.execute(query)
def get (self, query) :
"""
--- a/pvl/verkko/hosts.py Fri Oct 12 16:34:53 2012 +0300
+++ b/pvl/verkko/hosts.py Thu Oct 18 21:16:26 2012 +0300
@@ -1,8 +1,10 @@
from pvl.verkko import db, web
+from pvl.verkko.utils import parse_timedelta, IPv4Network
from pvl.html import tags as html
import re
+import datetime
import socket # dns
import logging; log = logging.getLogger('pvl.verkko.hosts')
@@ -16,6 +18,16 @@
MAC_RE = re.compile(MAC_SEP.join([MAC_HEX] * 6))
@classmethod
+ def query (cls, session, seen=None) :
+ """
+ seen - select hosts seen during given timedelta period
+ """
+
+ query = session.query(cls)
+
+ return query
+
+ @classmethod
def normalize_mac (cls, mac) :
match = cls.MAC_RE.search(mac)
@@ -45,6 +57,22 @@
return self.name.decode('ascii', 'replace')
else :
return None
+
+ STATES = {
+ 'DHCPACK': 'ack',
+ 'DHCPNAK': 'nak',
+ 'DHCPRELEASE': 'release',
+ 'DHCPDISCOVER': 'search',
+ 'DHCPREQUEST': 'search',
+ 'DHCPOFFER': 'search',
+ }
+
+ def state_class (self) :
+ if self.state in self.STATES :
+ return 'dhcp-' + self.STATES[self.state]
+
+ else :
+ return None
def when (self) :
return '{frm} - {to}'.format(
@@ -75,17 +103,18 @@
db.mapper(Host, db.dhcp_hosts, properties=dict(
id = db.dhcp_hosts.c.rowid,
- #_mac = db.dhcp_hosts.c.mac,
- #_name = db.dhcp_hosts.c.name,
+ state = db.dhcp_hosts.c.last_msg,
))
class BaseHandler (web.Handler) :
HOST_ATTRS = {
- 'id': Host.id,
- 'ip': Host.ip,
- 'mac': Host.mac,
- 'name': Host.name,
- 'seen': Host.last_seen,
+ 'id': Host.id,
+ 'net': Host.gw,
+ 'ip': Host.ip,
+ 'mac': Host.mac,
+ 'name': Host.name,
+ 'seen': Host.last_seen,
+ 'state': Host.state,
}
HOST_SORT = Host.last_seen.desc()
@@ -114,7 +143,9 @@
('IP', 'ip', 'ip', 'ip' ),
('MAC', 'mac', 'mac', 'mac' ),
('Hostname', 'name', False, False ),
- ('Seen', 'seen', False, False ),
+ ('Network', 'net', 'net', False ),
+ ('Seen', 'seen', 'seen', False ),
+ ('State', 'state', 'state', False ),
)
def url (**opts) :
@@ -167,7 +198,11 @@
)
),
html.td(host.render_name()),
+ html.td(
+ host.gw
+ ),
html.td(host.when()),
+ html.td(class_=host.state_class())(host.state),
) for i, host in enumerate(hosts)
),
html.tfoot(
@@ -190,12 +225,14 @@
def render_host (self, host, hosts) :
attrs = (
+ ('Network', host.gw),
('IP', host.ip),
('MAC', host.mac),
('Hostname', host.name),
('DNS', host.dns()),
('First seen', host.first_seen),
('Last seen', host.last_seen),
+ ('Last state', host.state),
)
return (
@@ -228,7 +265,7 @@
class ListHandler (BaseHandler) :
def process (self) :
- self.hosts = self.query()
+ hosts = self.query()
# filter?
self.filters = {}
@@ -239,25 +276,58 @@
if not value :
continue
- # preprocess
- like = False
-
- if value.endswith('*') :
- like = value.replace('*', '%')
-
- elif attr == 'mac' :
- value = Host.normalize_mac(value)
+ if attr == 'seen' :
+ if value.isdigit() :
+ # specific date
+ date = datetime.datetime.strptime(value, Host.DATE_FMT).date()
- # filter
- col = self.HOST_ATTRS[attr]
+ filter = db.between(date.strftime(Host.DATE_FMT),
+ db.func.strftime(Host.DATE_FMT, Host.first_seen),
+ db.func.strftime(Host.DATE_FMT, Host.last_seen)
+ )
+ else :
+ # recent
+ timedelta = parse_timedelta(value)
- if like :
- filter = (col.like(like))
+ # to seconds
+ timeout = timedelta.days * (24 * 60 * 60) + timedelta.seconds
+
+ # XXX: for sqlite, pgsql should handle this natively?
+ # WHERE strftime('%s', 'now') - strftime('%s', last_seen) < :timeout
+ filter = (db.func.strftime('%s', 'now') - db.func.strftime('%s', Host.last_seen) < timeout)
+
+ elif attr == 'ip' :
+ # parse as network expression
+ ip = IPv4Network(value)
+
+ if ip.masklen == 32 :
+ filter = (Host.ip == value)
+ else :
+ # XXX: column is IPv4 string literal format...
+ filter = ((Host.ip.op('&')(ip.mask)) == ip.base)
+
else :
- filter = (col == value)
+ # preprocess
+ like = False
+
+ if value.endswith('*') :
+ like = value.replace('*', '%')
+
+ elif attr == 'mac' :
+ value = Host.normalize_mac(value)
+
+ # filter
+ col = self.HOST_ATTRS[attr]
+
+ if like :
+ filter = (col.like(like))
+ else :
+ filter = (col == value)
- self.hosts = self.hosts.filter(filter)
+ hosts = hosts.filter(filter)
self.filters[attr] = value
+
+ self.hosts = hosts
def title (self) :
if self.filters :
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/pvl/verkko/utils.py Thu Oct 18 21:16:26 2012 +0300
@@ -0,0 +1,184 @@
+"""
+ DHCP... stuff
+"""
+
+import re
+import functools
+from datetime import datetime, timedelta
+
+TIMEDELTA_RE = re.compile(r'(\d+)([a-z]*)', re.IGNORECASE)
+TIMEDELTA_UNITS = {
+ 'd': 'days',
+ 'h': 'hours',
+ 'm': 'minutes',
+ 's': 'seconds',
+}
+
+def parse_timedelta (expr) :
+ """
+ Parse timeout -> timedelta
+
+ >>> parse_timedelta('1d')
+ datetime.timedelta(1)
+ >>> parse_timedelta('1h')
+ datetime.timedelta(0, 3600)
+ >>> parse_timedelta('15m')
+ datetime.timedelta(0, 900)
+ >>> parse_timedelta('1d1h1s')
+ datetime.timedelta(1, 3601)
+ """
+
+ what = {}
+
+ for (value, unit) in TIMEDELTA_RE.findall(expr) :
+ unit = unit.lower()
+ value = int(value)
+
+ if unit in TIMEDELTA_UNITS :
+ what[TIMEDELTA_UNITS[unit]] = value
+ else :
+ raise ValueError(unit)
+
+ return timedelta(**what)
+
+def timedelta_str (td):
+ """
+ datetime.timedelta -> short str
+
+ >>> print timedelta_str(timedelta(days=1))
+ 1d
+ >>> print timedelta_str(timedelta(hours=6))
+ 6h
+ >>> print timedelta_str(timedelta(days=2, hours=6, seconds=120))
+ 2d6h2m
+ """
+
+ # divmod
+ days = td.days
+
+ seconds = td.seconds
+ minutes, seconds = divmod(seconds, 60)
+ hours, minutes = divmod(minutes, 60)
+
+ # format
+ data = (
+ (days, 'd'),
+ (hours, 'h'),
+ (minutes, 'm'),
+ (seconds, 's'),
+ )
+
+ return ''.join('%d%s' % (count, unit) for count, unit in data if count)
+
+def parse_addr (addr, pad=False) :
+ """
+ Parse IPv4 addr -> int.
+
+ partial - allow partial addrs; right-pad with .0
+
+ >>> print "%#010x/%d" % parse_addr('1.2.3.4')
+ 0x01020304/32
+ >>> print "%#010x/%d" % parse_addr('1.2', pad=True)
+ 0x01020000/16
+
+ """
+
+ # split net
+ addr = [int(part) for part in addr.split('.') if part]
+
+ addrlen = len(addr) * 8
+
+ # fixup base?
+ if len(addr) == 4 :
+ # fine
+ pass
+
+ elif len(addr) > 4 :
+ raise ValueError("Invalid IPv4 net: {0}".format(addr))
+
+ elif len(addr) < 4 and pad :
+ # pad
+ addr += [0] * (4 - len(addr))
+
+ else :
+ raise ValueError("Incomplete IPv4 addr: {0}".format(addr))
+
+ # pack to int
+ return functools.reduce(lambda a, b: a * 256 + b, addr), addrlen
+
+def parse_net (expr) :
+ """
+ Parse given expr into (base, mask).
+
+ >>> print "%#010x/%#010x" % parse_net('1.2.3.4')
+ 0x01020304/0xffffffff
+ >>> print "%#010x/%#010x" % parse_net('1.2.0.0/16')
+ 0x01020000/0xffff0000
+ >>> print "%#010x/%#010x" % parse_net('1.2')
+ 0x01020000/0xffff0000
+ """
+
+ if '/' in expr :
+ net, masklen = expr.split('/', 1)
+
+ masklen = int(masklen)
+
+ else :
+ net = expr
+ masklen = None
+
+ base, baselen = parse_addr(net, pad=True)
+
+ if not masklen :
+ # implicit mask, by leaving off octets in the base
+ masklen = baselen
+
+ elif masklen > 32 :
+ raise ValueError("Invalid IPv4 mask: /{0:d}".format(masklen))
+
+ # pack
+ mask = (0xffffffff << (32 - masklen)) & 0xffffffff
+
+ # verify
+ if base & ~mask :
+ raise ValueError("Invalid IPv4 net base: {base:x} & {mask:x}".format(base=base, mask=mask))
+
+ return base, mask, masklen
+
+def IPv4Address (addr) :
+ """
+ Parse IPv4 address to int.
+ """
+
+ addr, len = parse_addr(addr)
+
+ return addr
+
+class IPv4Network (object) :
+ """
+ Parse and match network masks.
+
+ XXX: is used as a dict key
+ """
+
+ def __init__ (self, expr) :
+ self.expr = expr
+ self.base, self.mask, self.masklen = parse_net(expr)
+
+ def __contains__ (self, addr) :
+ return (addr & self.mask) == self.base
+
+ def __str__ (self) :
+ return self.expr
+
+ def __repr__ (self) :
+ return "IPv4Network(%r)" % (self.expr, )
+
+if __name__ == '__main__' :
+ import logging
+
+ logging.basicConfig()
+
+ import doctest
+ doctest.testmod()
+
--- a/static/style.css Fri Oct 12 16:34:53 2012 +0300
+++ b/static/style.css Thu Oct 18 21:16:26 2012 +0300
@@ -160,7 +160,7 @@
}
/*
- * Text
+ * Hosts
*/
.id
@@ -174,3 +174,8 @@
{
font-family: monospace;
}
+
+.dhcp-search { background-color: #444488; }
+.dhcp-ack { background-color: #448844; }
+.dhcp-nak { background-color: #884444; }
+.dhcp-release { background-color: #335533; }
--- a/test.py Fri Oct 12 16:34:53 2012 +0300
+++ b/test.py Thu Oct 18 21:16:26 2012 +0300
@@ -30,7 +30,7 @@
# common
parser.add_option_group(pvl.args.parser(parser))
- parser.add_option('-d', '--database-read', default='sqlite:///var/verkko.db',
+ parser.add_option('-d', '--database-read', metavar='URI', default='sqlite:///var/verkko.db',
help="Database to use (readonly)")
# parse