request per session, move to postgres, rename columns, fixup state/name/error handling in updates
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 math
import logging; log = logging.getLogger('pvl.verkko.hosts')
# XXX: this should actually be DHCPHost
class Host (object) :
DATE_FMT = '%Y%m%d'
MAC_HEX = r'([A-Za-z0-9]{2})'
MAC_SEP = r'[-:.]?'
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)
if not match :
raise ValueError(mac)
else :
return ':'.join(hh.lower() for hh in match.groups())
def __init__ (self, ip, mac, name=None) :
self.ip = ip
self.mac = mac
self.name = name
def render_mac (self) :
if not self.mac :
return None
elif len(self.mac) > (6 * 2 + 5) :
return u'???'
else :
return unicode(self.mac)
def render_name (self) :
if self.name :
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.error :
return 'dhcp-error'
elif self.state in self.STATES :
return 'dhcp-' + self.STATES[self.state]
else :
return None
def state_title (self) :
return self.error # or None
def render_state (self) :
if self.error :
return "{self.state}: {self.error}".format(self=self)
else :
return self.state
def when (self) :
return (
html.span(title=self.first_seen)(self.first_seen.strftime(self.DATE_FMT)),
'-',
html.span(title=self.last_seen)(self.last_seen.strftime(self.DATE_FMT)),
)
def dns (self) :
"""
Reverse-DNS lookup.
"""
if not self.ip :
return None
sockaddrs = set(sockaddr for family, socktype, proto, canonname, sockaddr in socket.getaddrinfo(self.ip, 0, 0, 0, 0, socket.AI_NUMERICHOST))
for sockaddr in sockaddrs :
try :
host, port = socket.getnameinfo(sockaddr, socket.NI_NAMEREQD)
except socket.gaierror :
continue
return host
def __unicode__ (self) :
return u"{host.ip} ({host.mac})".format(host=self)
db.mapper(Host, db.dhcp_hosts, properties=dict(
#id = db.dhcp_hosts.c.rowid,
#state = db.dhcp_hosts.c.,
))
class BaseHandler (web.Handler) :
HOST_ATTRS = {
'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()
def query (self) :
return self.db.query(Host)
def sort (self, hosts, default=HOST_SORT) :
self.sort = self.request.args.get('sort')
if self.sort :
sort = self.HOST_ATTRS[self.sort]
else :
sort = default
log.debug("sort: %s", sort)
hosts = hosts.order_by(sort)
# k
return hosts
def render_hosts (self, hosts, title=None, filters=False, page=None) :
COLS = (
#title sort filter class
('IP', 'ip', 'ip', 'ip' ),
('MAC', 'mac', 'mac', 'mac' ),
('Hostname', 'name', False, False ),
('Network', 'net', 'net', False ),
('Seen', 'seen', 'seen', False ),
('State', 'state', 'state', False ),
)
def url (**opts) :
args = dict()
if filters :
args.update(filters)
args.update(opts)
return self.url(**args)
def paginate (page, count=None) :
"""
Render pagination.
"""
if count is not None :
pages = int(math.ceil(count / self.PAGE)) # XXX: bad self.PAGE
else :
pages = None
if page > 0 :
yield html.a(href=url(page=0))(html("«« First"))
yield html.a(href=url(page=(page - 1)))(html("« Prev"))
yield html.span("Page {page} of {pages}".format(page=(page + 1), pages=(pages or '???')))
yield html.a(href=url(page=(page + 1)))(html("» Next"))
table = html.table(
html.caption(title) if title else None,
html.thead(
html.tr(
html.th('#'),
(
html.th(
html.a(href=url(sort=sort))(title) if sort else (title)
) for title, sort, filter, class_ in COLS
)
),
html.tr(class_='filter')(
html.td(
html.input(type='submit', value=u'\u00BF'),
),
(
html.td(class_=class_)(
html.input(type='text', name=filter, value=filters.get(filter)) if filter else None
) for title, sort, filter, class_ in COLS
)
) if filters is not False else None
),
html.tbody(
html.tr(class_=('alternate' if i % 2 else None), id=host.id)(
html.th(
html.a(href=self.url(ItemHandler, id=host.id))(
'#' #host.id
)
),
html.td(class_='ip')(
html.a(href=self.url(ListHandler, ip=host.ip))(
host.ip
)
),
html.td(class_='mac')(
html.a(href=self.url(ListHandler, mac=host.mac))(
host.render_mac()
)
),
html.td(host.render_name()),
html.td(
host.gw
),
html.td(host.when()),
html.td(class_=host.state_class(), title=host.state_title())(host.state),
) for i, host in enumerate(hosts)
),
html.tfoot(
html.tr(
html.td(colspan=(1 + len(COLS)))(
paginate(page) if page is not None else (
# XXX: does separate SELECT count()
"{count} hosts".format(count=hosts.count())
)
)
)
)
)
if filters is False :
return table
else :
return html.form(method='get', action=self.url())(
html.input(type='hidden', name='sort', value=self.sort),
table,
)
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.render_state()),
)
return (
html.h2('Host'),
html.dl(
(html.dt(title), html.dd(value)) for title, value in attrs
),
html.h2('Related'),
self.render_hosts(hosts),
html.a(href=self.url(ListHandler))(html('«'), 'Back'),
)
class ItemHandler (BaseHandler) :
def process (self, id) :
self.hosts = self.query()
self.host = self.hosts.get(id)
if not self.host :
raise web.NotFound("No such host: {id}".format(id=id))
self.hosts = self.sort(self.hosts.filter((Host.ip == self.host.ip) | (Host.mac == self.host.mac)))
def title (self) :
return u"DHCP Host: {self.host}".format(self=self)
def render (self) :
return self.render_host(self.host, self.hosts)
class ListHandler (BaseHandler) :
# pagination
PAGE = 10
def process (self) :
hosts = self.query()
# filter?
column = None
self.filters = {}
for attr in self.HOST_ATTRS :
value = self.request.args.get(attr)
if not value :
continue
if attr == 'seen' :
column = Host.last_seen
if value.isdigit() :
# specific date
date = datetime.datetime.strptime(value, Host.DATE_FMT).date()
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)
filter = ((db.func.now() - Host.last_seen) < timedelta)
# XXX: for sqlite, pgsql should handle this natively?
# to seconds
#timeout = timedelta.days * (24 * 60 * 60) + timedelta.seconds
# 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' :
column = Host.ip
# column is IPv4 string literal format...
if '/' in value :
filter = (db.func.inet(Host.ip).op('<<')(db.func.cidr(value)))
else :
filter = (db.func.inet(Host.ip) == db.func.inet(value))
else :
# preprocess
like = False
if value.endswith('*') :
like = value.replace('*', '%')
elif attr == 'mac' :
value = Host.normalize_mac(value)
# filter
column = self.HOST_ATTRS[attr]
if like :
filter = (column.like(like))
else :
filter = (column == value)
hosts = hosts.filter(filter)
self.filters[attr] = value
# sort XXX: default per filter column
hosts = self.sort(hosts) #, column)
# page?
self.page = self.request.args.get('page')
if self.page :
self.page = int(self.page)
hosts = hosts.offset(self.page * self.PAGE).limit(self.PAGE)
self.hosts = hosts
def title (self) :
if self.filters :
return "DHCP Hosts: {filters}".format(filters=', '.join(self.filters.itervalues()))
else :
return "DHCP Hosts"
def render (self) :
return (
self.render_hosts(self.hosts, filters=self.filters, page=self.page),
html.a(href=self.url())(html('«'), 'Back') if self.filters else None,
)