from pvl.verkko import web, db
from pvl.verkko.utils import parse_timedelta, IPv4Network
from pvl.web import html
import re
import datetime
import socket # dns
import math
import logging; log = logging.getLogger('pvl.verkko.hosts')
## Model
import json
import time
def dt2ts (dt) :
return int(time.mktime(dt.timetuple()))
def ts2dt (ts) :
return datetime.datetime.fromtimestamp(ts)
# TODO: this should be DHCPHost
class Host (object) :
DATE_FMT = '%Y%m%d'
TIME_FMT = '%H:%M:%S'
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 network (self) :
return self.gw
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
@classmethod
def format_datetime (cls, dt) :
if (datetime.datetime.now() - dt).days :
return dt.strftime(cls.DATE_FMT)
else :
return dt.strftime(cls.TIME_FMT)
def seen (self) :
return (
html.span(title=self.first_seen)(self.format_datetime(self.first_seen)),
'-',
html.span(title=self.last_seen)(self.format_datetime(self.last_seen))
)
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.,
))
## Controller
def column (attr, title, column, rowhtml=None, sort=True, filter=True, colcss=True, rowfilter=True, rowtitle=None, rowcss=None) :
"""
web.Table column spec.
"""
return (attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss)
class BaseHandler (web.DatabaseHandler) :
"""
Common controller stuff for DHCP hosts
"""
CSS = (
"/static/dhcp/hosts.css",
)
JS = (
#"/static/jquery/jquery.js"
)
TABLE = Host
TABLE_COLUMNS = (
#column('id', "#", Host.id ),
column('ip', "IP", Host.ip, ),
column('mac', "MAC", Host.mac, Host.render_mac),
column('name', "Hostname", Host.name, Host.render_name, rowfilter=False),
column('gw', "Network", Host.gw, Host.network, rowfilter=False),
column('seen', "Seen", Host.last_seen, Host.seen, rowfilter=False),
column('state', "State", Host.count, rowtitle=Host.state_title, rowcss=Host.state_class, rowfilter=False),
)
# attr -> column
TABLE_ATTRS = dict((attr, column) for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in TABLE_COLUMNS)
# default sort
TABLE_SORT = Host.last_seen.desc()
# items per page
TABLE_PAGE = 10
# target for items
TABLE_URL = None
TABLE_ITEM_URL = None
def query (self) :
"""
Database SELECT query.
"""
return self.db.query(self.TABLE)
def sort (self, query, default=TABLE_SORT) :
"""
Apply ?sort= from requset args to query.
Return { attr: sort }, query
"""
sort = self.request.args.get('sort')
if sort :
name = sort.lstrip('+-')
else :
name = None
if name :
order_by = self.TABLE_ATTRS[name]
else :
order_by = default
# prefix -> ordering
if not sort :
pass
elif sort.startswith('+') :
order_by = order_by.asc()
elif sort.startswith('-') :
order_by = order_by.desc()
else :
pass
# apply
log.debug("sort: %s", order_by)
query = query.order_by(order_by)
return sort, query
def filter_seen (self, value) :
"""
Return filter expression for given attr == value
"""
column = Host.last_seen
if value.isdigit() :
# specific date
date = datetime.datetime.strptime(value, Host.DATE_FMT).date()
return 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)
return ((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)
def filter_ip (self, value) :
column = Host.ip
# column is IPv4 string literal format...
if '/' in value :
return (db.func.inet(Host.ip).op('<<')(db.func.cidr(value)))
else :
return (db.func.inet(Host.ip) == db.func.inet(value))
def filter_mac (self, value) :
return self.filter_attr('mac', Host.normalize_mac(value))
def filter_attr (self, attr, value) :
"""
Return filter expression for given attr == value
"""
# preprocess
like = False
if value.endswith('*') :
like = value.replace('*', '%')
# filter
column = self.TABLE_ATTRS[attr]
if like :
return (column.like(like))
else :
return (column == value)
def _filter (self, attr, values) :
"""
Apply filters for given attr -> (value, expression)
"""
for value in values :
value = value.strip()
# ignore empty fields
if not value :
continue
# lookup attr-specific filter
filter = getattr(self, 'filter_{attr}'.format(attr=attr), None)
if filter :
filter = filter(value)
else :
# use generic
filter = self.filter_attr(attr, value)
log.debug("%s: %s: %s", attr, value, filter)
yield value, filter
def filter (self, query) :
"""
Apply filters from request.args against given hosts.
Returns (filters, hosts).
"""
# filter?
filters = {}
for attr in self.TABLE_ATTRS :
# from request args
values = self.request.args.getlist(attr)
# lookup attr filters as expressions
value_filters = list(self._filter(attr, values))
# ignore empty fields
if not value_filters :
continue
# filtering values, and filter expressions
values, expressions = zip(*value_filters)
# apply
query = query.filter(db.or_(*expressions))
filters[attr] = values
return filters, query
def filters_title (self) :
"""
Return a string representing the applied filters.
"""
return ', '.join(value for values in self.filters.itervalues() for value in values)
def render_table (self, query, caption=None, sort=None, filters=None, page=None, hilight=None) :
"""
Return <table> element. Wrapped in <form> if filters.
query - filter()'d sort()'d SELECT query()
caption - optional <caption>
sort - None for no sorting ui, sort-attr otherwise.
filters - None for no filtering ui, dict of filters otherwise.
page - display pagination for given page
hilight - { attr: value } cells to hilight
"""
def url (filters=filters, sort=sort, **opts) :
"""
URL for table with given opts, keeping our sorting/filtering unless overriden.
"""
args = dict()
if filters :
args.update(filters)
if sort :
args['sort'] = sort
if opts :
args.update(opts)
return self.url(self.TABLE_URL, **args)
def sorturl (attr, sort=sort) :
"""
URL for table sorted by given column, reversing direction if already sorting by given column.
"""
if not sort :
sort = attr
elif sort.lstrip('+-') != attr :
sort = attr
elif sort.startswith('-') :
sort = "+" + attr
else :
sort = "-" + attr
return url(sort=sort)
def itemurl (item) :
"""
URL for given item, by id.
"""
if self.TABLE_ITEM_URL :
# separate page
return self.url(self.TABLE_ITEM_URL, id=item.id)
else :
# to our table
return url() + '#{id}'.format(id=item.id)
def render_filter (attr) :
"""
Render filter-input for column.
"""
value = filters.get(attr)
if value :
# XXX: multi-valued filters?
value = value[0]
else :
value = None
return html.input(type='text', name=attr, value=value)
def render_head () :
"""
Yield header, filter rows for columns in table header.
"""
# id
yield html.td('#'), html.td(html.input(type='submit', value=u'\u00BF'))
for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in self.TABLE_COLUMNS :
header = title
if sort :
header = html.a(href=sorturl(attr))(header)
header = html.th(header)
if filters is not None and filter :
filter = render_filter(attr)
else :
filter = None
if colcss is True :
colcss = attr
filter = html.td(class_=colcss)(filter)
yield header, filter
def render_cell (attr, value, rowhtml=None, colcss=True, filter=None, rowtitle=None, rowcss=None, hilight=hilight) :
"""
Render a single cell.
colcss - css class for column; True -> attr
filter - render filter link for value?
htmlvalue - rendered value?
title - mouseover title for cell
rowcss - css class for row
"""
if not rowhtml :
rowhtml = value
if filter :
cell = html.a(href=url(filters=None, **{attr: value}))(rowhtml)
else :
cell = rowhtml
if colcss is True :
colcss = attr
if hilight :
hilight = attr in hilight and value in hilight[attr]
css = (colcss, rowcss, 'hilight' if hilight else None)
css = ' '.join(cls for cls in css if cls)
return html.td(class_=css, title=rowtitle)(cell)
def render_row (item) :
"""
Yield columns for row.
"""
for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in self.TABLE_COLUMNS :
# XXX: this is sometimes broken, figure out how to index by column
value = getattr(item, attr)
if rowhtml :
rowhtml = rowhtml(item)
else :
rowhtml = value
if rowtitle :
rowtitle = rowtitle(item)
else :
rowtitle = None
if rowcss :
rowcss = rowcss(item)
else :
rowcss = None
yield render_cell(attr, value,
rowhtml = rowhtml,
colcss = colcss,
filter = value if rowfilter else None,
rowtitle = rowtitle,
rowcss = rowcss,
)
def render_body (rows) :
"""
Yield rows.
"""
for i, item in enumerate(rows) :
yield html.tr(class_=('alternate' if i % 2 else None), id=item.id)(
html.th(
html.a(href=itemurl(item))("#")
),
render_row(item)
)
def render_pagination (page, count=None) :
"""
Render pagination links.
"""
if count is not None :
pages = int(math.ceil(count / self.TABLE_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"))
def render_foot () :
# XXX: does separate SELECT count()
count = query.count()
if page :
return render_pagination(page, count)
else :
return "{count} hosts".format(count=count)
# columns for the two header rows
headers, filtering = zip(*list(render_head()))
# render table
table = html.table(
html.caption(caption) if caption else None,
html.thead(
html.tr(headers),
# filters?
html.tr(class_='filter')(filtering) if filters is not None else None,
),
html.tbody(
render_body(query)
),
html.tfoot(
html.tr(
html.td(colspan=(1 + len(self.TABLE_COLUMNS)))(
render_foot()
)
)
)
)
# filters form?
if filters is None :
return table
else :
return html.form(method='get', action=url(filters=None, sort=None))(
html.input(type='hidden', name='sort', value=sort),
table,
)
class ItemHandler (BaseHandler) :
"""
A specific DHCP host, along with a list of related hosts.
"""
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.sorts, 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_host (self, host) :
"""
Details for specific host.
"""
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()),
('Total messages', host.count),
)
return (
html.dl(
(html.dt(title), html.dd(value)) for title, value in attrs
)
)
def render (self) :
return (
html.h2('Host'),
self.render_host(self.host),
html.h2('Related'),
self.render_table(self.hosts, sort=self.sorts, hilight=dict(ip=self.host.ip, mac=self.host.mac)),
html.a(href=self.url(ListHandler))(html('«'), 'Back'),
)
class ListHandler (BaseHandler) :
"""
List of DHCP hosts for given filter.
"""
TABLE_PAGE = 10
def process (self) :
hosts = self.query()
# filter
self.filters, hosts = self.filter(hosts)
# sort XXX: default per filter column?
self.sorts, hosts = self.sort(hosts)
# 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=self.filters_title())
else :
return "DHCP Hosts"
def render (self) :
return (
self.render_table(self.hosts, filters=self.filters, sort=self.sorts, page=self.page),
html.a(href=self.url())(html('«'), 'Back') if self.filters else None,
)
# XXX:
BaseHandler.TABLE_URL = ListHandler
BaseHandler.TABLE_ITEM_URL = ItemHandler
class RealtimeHandler (BaseHandler) :
TITLE = "DHCP Pseudo-Realtime hosts.."
CSS = BaseHandler.CSS + (
'http://code.jquery.com/ui/1.9.0/themes/base/jquery-ui.css',
)
JS = (
#"/static/jquery/jquery.js",
'http://code.jquery.com/jquery-1.8.2.js',
'http://code.jquery.com/ui/1.9.0/jquery-ui.js',
'/static/dhcp/spin.js',
'/static/dhcp/hosts.js',
)
COLUMNS = (
( 'ip', 'IP', lambda host: host.ip ),
( 'mac', 'MAC', lambda host: host.mac ),
( 'name', 'Hostname', lambda host: host.name ),
( 'gw', 'Network', lambda host: host.gw ),
( 'seen', 'Seen', Host.seen, ),
( 'state', 'State', lambda host: host.state ),
)
def process (self) :
"""
Either return JSON (if ?t=...), or fetch hosts/t for rendering.
"""
hosts = self.db.query(Host)
t = self.request.args.get('t')
# always sorted by last_seen
hosts = hosts.order_by(Host.last_seen.desc())
# filter
self.filters, hosts = self.filter(hosts)
def host_params (host) :
yield 'id', host.id
for name, title, fvalue in self.COLUMNS :
value = fvalue(host)
if name == 'seen' :
# XXX: hackfix html() rendering
value = unicode(html.div(value))
yield name, value
# special
yield 'state_class', host.state_class()
if t :
# return json
t = ts2dt(int(t))
hosts = hosts.filter(Host.last_seen > t)
hosts = list(hosts)
hosts.reverse()
if hosts :
t = hosts[-1].last_seen
hosts = [dict(host_params(host)) for host in hosts]
else :
hosts = []
data = dict(
t = dt2ts(t),
hosts = hosts,
)
return web.Response(json.dumps(data), mimetype='text/json')
else :
# render html
hosts = hosts.limit(10)
# XXX: testing
hosts = hosts.offset(1)
self.hosts = list(hosts)
if self.hosts :
self.t = self.hosts[0].last_seen
else :
self.t = datetime.datetime.now()
def title (self) :
if self.filters :
return "{title}: {filters}".format(title=self.TITLE, filters=self.filters_title())
else :
return self.TITLE
def render (self) :
"""
Render page HTML and initial <table>, along with bootstrap JS (t0, configuration).
"""
def column (name, title, fvalue, host) :
cls = name
if name == 'state' :
cls = host.state_class()
return html.td(class_=cls)(fvalue(host))
params = dict(
url = self.url(),
filters = self.filters,
t = dt2ts(self.t),
host = self.url(ItemHandler, id='0'),
columns = [name for name, title, fvalue in self.COLUMNS]
)
params = json.dumps(params)
return html.div(id='wrapper')(
html.input(type='submit', id='refresh', value="Refresh"),
html.input(type='reset', id='pause', value="Pause"),
html.table(id='hosts')(
html.thead(
html.tr(
html.th('#'),
(
html.th(class_=name)(title) for name, title, fvalue in self.COLUMNS
)
),
),
html.tbody(
html.tr(id=host.id)(
html.td(html.a(href=self.url(ItemHandler, id=host.id))('#')),
(
column(name, title, fvalue, host) for name, title, fvalue in self.COLUMNS
),
) for host in self.hosts
)
),
html.script(type='text/javascript')("""
$(document).ready(hosts_realtime({params}));
""".format(params=params)
)
)