terom@0: from pvl.verkko import db, web terom@14: from pvl.verkko.utils import parse_timedelta, IPv4Network terom@0: terom@0: from pvl.html import tags as html terom@0: terom@8: import re terom@14: import datetime terom@0: import socket # dns terom@16: import math terom@0: terom@0: import logging; log = logging.getLogger('pvl.verkko.hosts') terom@0: terom@1: # XXX: this should actually be DHCPHost terom@0: class Host (object) : terom@0: DATE_FMT = '%Y%m%d' terom@8: terom@8: MAC_HEX = r'([A-Za-z0-9]{2})' terom@8: MAC_SEP = r'[-:.]?' terom@8: MAC_RE = re.compile(MAC_SEP.join([MAC_HEX] * 6)) terom@8: terom@8: @classmethod terom@14: def query (cls, session, seen=None) : terom@14: """ terom@14: seen - select hosts seen during given timedelta period terom@14: """ terom@14: terom@14: query = session.query(cls) terom@14: terom@14: return query terom@14: terom@14: @classmethod terom@8: def normalize_mac (cls, mac) : terom@8: match = cls.MAC_RE.search(mac) terom@8: terom@8: if not match : terom@8: raise ValueError(mac) terom@8: terom@8: else : terom@8: return ':'.join(hh.lower() for hh in match.groups()) terom@0: terom@0: def __init__ (self, ip, mac, name=None) : terom@0: self.ip = ip terom@0: self.mac = mac terom@0: self.name = name terom@0: terom@0: def render_mac (self) : terom@0: if not self.mac : terom@0: return None terom@0: terom@0: elif len(self.mac) > (6 * 2 + 5) : terom@0: return u'???' terom@0: terom@0: else : terom@0: return unicode(self.mac) terom@0: terom@0: def render_name (self) : terom@0: if self.name : terom@0: return self.name.decode('ascii', 'replace') terom@0: else : terom@0: return None terom@14: terom@14: STATES = { terom@14: 'DHCPACK': 'ack', terom@14: 'DHCPNAK': 'nak', terom@14: 'DHCPRELEASE': 'release', terom@14: 'DHCPDISCOVER': 'search', terom@14: 'DHCPREQUEST': 'search', terom@14: 'DHCPOFFER': 'search', terom@14: } terom@14: terom@14: def state_class (self) : terom@16: if self.error : terom@16: return 'dhcp-error' terom@16: terom@16: elif self.state in self.STATES : terom@14: return 'dhcp-' + self.STATES[self.state] terom@14: terom@14: else : terom@14: return None terom@0: terom@16: def state_title (self) : terom@16: return self.error # or None terom@16: terom@16: def render_state (self) : terom@16: if self.error : terom@16: return "{self.state}: {self.error}".format(self=self) terom@16: else : terom@16: return self.state terom@16: terom@26: def seen (self) : terom@16: return ( terom@16: html.span(title=self.first_seen)(self.first_seen.strftime(self.DATE_FMT)), terom@16: '-', terom@16: html.span(title=self.last_seen)(self.last_seen.strftime(self.DATE_FMT)), terom@0: ) terom@0: terom@0: def dns (self) : terom@0: """ terom@0: Reverse-DNS lookup. terom@0: """ terom@0: terom@0: if not self.ip : terom@0: return None terom@0: terom@0: sockaddrs = set(sockaddr for family, socktype, proto, canonname, sockaddr in socket.getaddrinfo(self.ip, 0, 0, 0, 0, socket.AI_NUMERICHOST)) terom@0: terom@0: for sockaddr in sockaddrs : terom@0: try : terom@0: host, port = socket.getnameinfo(sockaddr, socket.NI_NAMEREQD) terom@0: except socket.gaierror : terom@0: continue terom@0: terom@0: return host terom@0: terom@0: def __unicode__ (self) : terom@0: return u"{host.ip} ({host.mac})".format(host=self) terom@0: terom@0: db.mapper(Host, db.dhcp_hosts, properties=dict( terom@16: #id = db.dhcp_hosts.c.rowid, terom@16: #state = db.dhcp_hosts.c., terom@0: )) terom@0: terom@26: terom@26: terom@26: terom@5: class BaseHandler (web.Handler) : terom@5: HOST_ATTRS = { terom@14: 'id': Host.id, terom@14: 'net': Host.gw, terom@14: 'ip': Host.ip, terom@14: 'mac': Host.mac, terom@14: 'name': Host.name, terom@14: 'seen': Host.last_seen, terom@14: 'state': Host.state, terom@17: 'count': Host.count, terom@5: } terom@0: terom@5: HOST_SORT = Host.last_seen.desc() terom@0: terom@5: def query (self) : terom@16: return self.db.query(Host) terom@16: terom@16: def sort (self, hosts, default=HOST_SORT) : terom@9: self.sort = self.request.args.get('sort') terom@0: terom@9: if self.sort : terom@9: sort = self.HOST_ATTRS[self.sort] terom@3: else : terom@16: sort = default terom@0: terom@3: log.debug("sort: %s", sort) terom@16: terom@3: hosts = hosts.order_by(sort) terom@3: terom@5: # k terom@5: return hosts terom@5: terom@21: def render_hosts (self, hosts, title=None, filters=False, page=None, hilight=None) : terom@5: COLS = ( terom@10: #title sort filter class terom@10: ('IP', 'ip', 'ip', 'ip' ), terom@10: ('MAC', 'mac', 'mac', 'mac' ), terom@10: ('Hostname', 'name', False, False ), terom@14: ('Network', 'net', 'net', False ), terom@14: ('Seen', 'seen', 'seen', False ), terom@14: ('State', 'state', 'state', False ), terom@5: ) terom@4: terom@9: def url (**opts) : terom@11: args = dict() terom@11: terom@11: if filters : terom@11: args.update(filters) terom@11: terom@9: args.update(opts) terom@9: terom@9: return self.url(**args) terom@9: terom@16: def paginate (page, count=None) : terom@16: """ terom@16: Render pagination. terom@16: """ terom@16: terom@16: if count is not None : terom@16: pages = int(math.ceil(count / self.PAGE)) # XXX: bad self.PAGE terom@16: else : terom@16: pages = None terom@16: terom@16: if page > 0 : terom@16: yield html.a(href=url(page=0))(html("«« First")) terom@16: yield html.a(href=url(page=(page - 1)))(html("« Prev")) terom@16: terom@16: yield html.span("Page {page} of {pages}".format(page=(page + 1), pages=(pages or '???'))) terom@16: terom@16: yield html.a(href=url(page=(page + 1)))(html("» Next")) terom@16: terom@19: def render_filter (filter) : terom@19: value = filters.get(filter) terom@19: terom@19: if value : terom@19: # XXX: multi-valued filters? terom@19: value = value[0] terom@19: else : terom@19: value = None terom@19: terom@19: return html.input(type='text', name=filter, value=value) terom@16: terom@21: def render_cell (attr, value, cssclass=True, filter=None, htmlvalue=None) : terom@21: if htmlvalue : terom@21: cell = htmlvalue terom@21: else : terom@21: cell = value terom@21: terom@21: if filter : terom@22: cell = html.a(href=self.url(ListHandler, **{attr: value}))(value) terom@21: terom@21: if cssclass is True : terom@21: cssclass = attr terom@21: terom@21: css = (cssclass, 'hilight' if (hilight and attr in hilight and value in hilight[attr]) else None) terom@21: css = ' '.join(cls for cls in css if cls) terom@21: terom@21: return html.td(class_=css)(cell) terom@21: terom@9: table = html.table( terom@5: html.caption(title) if title else None, terom@5: html.thead( terom@5: html.tr( terom@9: html.th('#'), terom@9: ( terom@9: html.th( terom@9: html.a(href=url(sort=sort))(title) if sort else (title) terom@10: ) for title, sort, filter, class_ in COLS terom@9: ) terom@9: ), terom@9: html.tr(class_='filter')( terom@9: html.td( terom@9: html.input(type='submit', value=u'\u00BF'), terom@9: ), terom@9: ( terom@10: html.td(class_=class_)( terom@19: render_filter(filter) if filter else None terom@10: ) for title, sort, filter, class_ in COLS terom@9: ) terom@9: ) if filters is not False else None terom@5: ), terom@5: html.tbody( terom@5: html.tr(class_=('alternate' if i % 2 else None), id=host.id)( terom@11: html.th( terom@6: html.a(href=self.url(ItemHandler, id=host.id))( terom@16: '#' #host.id terom@5: ) terom@5: ), terom@21: terom@21: render_cell('ip', host.ip, filter=True), terom@21: render_cell('mac', host.mac, filter=True, htmlvalue=host.render_mac()), terom@21: render_cell('name', host.name, htmlvalue=host.render_name()), terom@21: render_cell('gw', host.gw), terom@21: terom@26: html.td(host.seen()), terom@16: html.td(class_=host.state_class(), title=host.state_title())(host.state), terom@5: ) for i, host in enumerate(hosts) terom@12: ), terom@12: html.tfoot( terom@12: html.tr( terom@12: html.td(colspan=(1 + len(COLS)))( terom@16: paginate(page) if page is not None else ( terom@16: # XXX: does separate SELECT count() terom@16: "{count} hosts".format(count=hosts.count()) terom@16: ) terom@12: ) terom@12: ) terom@5: ) terom@5: ) terom@9: terom@9: if filters is False : terom@9: return table terom@9: else : terom@9: return html.form(method='get', action=self.url())( terom@9: html.input(type='hidden', name='sort', value=self.sort), terom@9: table, terom@9: ) terom@4: terom@5: class ItemHandler (BaseHandler) : terom@5: def process (self, id) : terom@5: self.hosts = self.query() terom@5: self.host = self.hosts.get(id) terom@5: terom@5: if not self.host : terom@5: raise web.NotFound("No such host: {id}".format(id=id)) terom@5: terom@16: self.hosts = self.sort(self.hosts.filter((Host.ip == self.host.ip) | (Host.mac == self.host.mac))) terom@5: terom@5: def title (self) : terom@5: return u"DHCP Host: {self.host}".format(self=self) terom@5: terom@20: def render_host (self, host) : terom@20: """ terom@20: Details for specific host. terom@20: """ terom@20: terom@20: attrs = ( terom@20: ('Network', host.gw), terom@20: ('IP', host.ip), terom@20: ('MAC', host.mac), terom@20: ('Hostname', host.name), terom@20: ('DNS', host.dns()), terom@20: ('First seen', host.first_seen), terom@20: ('Last seen', host.last_seen), terom@20: ('Last state', host.render_state()), terom@20: ('Total messages', host.count), terom@20: ) terom@20: terom@20: return ( terom@20: html.dl( terom@20: (html.dt(title), html.dd(value)) for title, value in attrs terom@20: ) terom@20: ) terom@20: terom@5: def render (self) : terom@20: return ( terom@20: html.h2('Host'), terom@20: self.render_host(self.host), terom@20: terom@20: html.h2('Related'), terom@21: self.render_hosts(self.hosts, hilight=dict(ip=self.host.ip, mac=self.host.mac)), terom@20: terom@20: html.a(href=self.url(ListHandler))(html('«'), 'Back'), terom@20: ) terom@20: terom@5: terom@5: class ListHandler (BaseHandler) : terom@16: # pagination terom@16: PAGE = 10 terom@16: terom@19: # views terom@19: VIEWS = ( terom@19: ("Last hour", dict(seen='1h')), terom@19: ("Last day", dict(seen='24h')), terom@19: ("All", dict()), terom@19: ) + tuple( terom@19: ("Network " + network, dict(ip=network)) for network in ( terom@19: '194.197.235.0/24', terom@19: '10.1.0.0/16', terom@19: '10.4.0.0/16', terom@19: '10.5.0.0/16', terom@19: '10.6.0.0/16', terom@19: '10.10.0.0/16', terom@19: ) terom@19: ) + ( terom@19: ("Valid", dict(state=('DHCPACK', 'DHCPRELEASE'))), terom@19: ("Incomplete", dict(state=('DHCPDISCOVER', 'DHCPOFFER', 'DHCPREQUEST'))), terom@19: ("Invalid", dict(state=('DHCPNAK', ))), terom@19: ) terom@19: terom@19: def filter (self, attr, value) : terom@19: """ terom@19: Return filter expression for given attr == value terom@19: """ terom@19: terom@19: if attr == 'seen' : terom@19: column = Host.last_seen terom@19: terom@19: if value.isdigit() : terom@19: # specific date terom@19: date = datetime.datetime.strptime(value, Host.DATE_FMT).date() terom@19: terom@19: return db.between(date.strftime(Host.DATE_FMT), terom@19: db.func.strftime(Host.DATE_FMT, Host.first_seen), terom@19: db.func.strftime(Host.DATE_FMT, Host.last_seen) terom@19: ) terom@19: else : terom@19: # recent terom@19: timedelta = parse_timedelta(value) terom@19: terom@19: return ((db.func.now() - Host.last_seen) < timedelta) terom@19: terom@19: # XXX: for sqlite, pgsql should handle this natively? terom@19: # to seconds terom@19: #timeout = timedelta.days * (24 * 60 * 60) + timedelta.seconds terom@19: terom@19: # WHERE strftime('%s', 'now') - strftime('%s', last_seen) < :timeout terom@19: #filter = (db.func.strftime('%s', 'now') - db.func.strftime('%s', Host.last_seen) < timeout) terom@19: terom@19: elif attr == 'ip' : terom@19: column = Host.ip terom@19: terom@19: # column is IPv4 string literal format... terom@19: if '/' in value : terom@19: return (db.func.inet(Host.ip).op('<<')(db.func.cidr(value))) terom@19: else : terom@19: return (db.func.inet(Host.ip) == db.func.inet(value)) terom@19: terom@19: else : terom@19: # preprocess terom@19: like = False terom@19: terom@19: if value.endswith('*') : terom@19: like = value.replace('*', '%') terom@19: terom@19: elif attr == 'mac' : terom@19: value = Host.normalize_mac(value) terom@19: terom@19: # filter terom@19: column = self.HOST_ATTRS[attr] terom@19: terom@19: if like : terom@19: return (column.like(like)) terom@19: else : terom@19: return (column == value) terom@19: terom@8: def process (self) : terom@14: hosts = self.query() terom@9: terom@9: # filter? terom@8: self.filters = {} terom@5: terom@8: for attr in self.HOST_ATTRS : terom@19: values = self.request.args.getlist(attr) terom@8: terom@19: if not values : terom@19: continue terom@14: terom@19: filter = db.or_(*[self.filter(attr, value) for value in values]) terom@14: terom@19: log.debug("filter %s: %s", attr, filter) terom@14: terom@14: hosts = hosts.filter(filter) terom@19: self.filters[attr] = values terom@16: terom@19: # sort XXX: default per filter column? terom@19: hosts = self.sort(hosts) terom@16: terom@16: # page? terom@16: self.page = self.request.args.get('page') terom@16: terom@16: if self.page : terom@16: self.page = int(self.page) terom@16: terom@16: hosts = hosts.offset(self.page * self.PAGE).limit(self.PAGE) terom@16: terom@14: self.hosts = hosts terom@5: terom@5: def title (self) : terom@8: if self.filters : terom@19: return "DHCP Hosts: {filters}".format(filters=', '.join(value for values in self.filters.itervalues() for value in values)) terom@8: else : terom@8: return "DHCP Hosts" terom@5: terom@5: def render (self) : terom@8: return ( terom@16: self.render_hosts(self.hosts, filters=self.filters, page=self.page), terom@8: terom@8: html.a(href=self.url())(html('«'), 'Back') if self.filters else None, terom@8: ) terom@25: terom@25: import json terom@25: import time terom@25: terom@25: def dt2ts (dt) : terom@25: return int(time.mktime(dt.timetuple())) terom@25: terom@25: def ts2dt (ts) : terom@25: return datetime.datetime.fromtimestamp(ts) terom@25: terom@25: class RealtimeHandler (web.Handler) : terom@25: TITLE = "Pseudo-Realtime hosts.." terom@25: JS = ( terom@25: #"/static/jquery/jquery.js", terom@25: 'http://code.jquery.com/jquery-1.8.2.js', terom@26: '/static/js/spin.js', terom@25: '/static/hosts.js', terom@25: ) terom@25: terom@25: def process (self) : terom@25: hosts = self.db.query(Host).order_by(Host.last_seen.desc()) terom@25: t = self.request.args.get('t') terom@25: terom@25: if t : terom@25: t = ts2dt(int(t)) terom@25: terom@25: # update terom@25: hosts = hosts.filter(Host.last_seen > t) terom@25: hosts = list(hosts) terom@25: hosts.reverse() terom@25: terom@25: if hosts : terom@25: t = hosts[-1].last_seen terom@26: hosts = [dict( terom@26: id = host.id, terom@26: ip = host.ip, terom@26: mac = host.mac, terom@26: name = host.name, terom@26: gw = host.gw, terom@26: seen = unicode(html.div(host.seen())), terom@26: state = host.state, terom@26: state_class = host.state_class(), terom@26: terom@26: t = dt2ts(host.last_seen), terom@26: ) for host in hosts] terom@25: terom@25: else : terom@25: hosts = [] terom@25: terom@25: data = dict( terom@25: t = dt2ts(t), terom@25: hosts = hosts, terom@25: ) terom@25: terom@25: return web.Response(json.dumps(data), mimetype='text/json') terom@25: terom@25: else : terom@25: self.hosts = hosts.limit(10) terom@25: terom@25: # XXX: testing terom@25: self.hosts = self.hosts.offset(1) terom@25: terom@25: self.t = self.hosts[0].last_seen terom@25: terom@25: def render (self) : terom@25: params = dict( terom@25: url = self.url(), terom@25: t = dt2ts(self.t), terom@26: host = self.url(ItemHandler, id='0'), terom@25: ) terom@25: params = json.dumps(params) terom@26: terom@26: COLUMNS = ( terom@26: '#', 'IP', 'MAC', 'Hostname', 'Network', 'Seen', 'State' terom@26: ) terom@25: terom@26: return html.div(id='wrapper')( terom@26: html.input(type='submit', id='refresh', value="Refresh"), terom@26: html.table(id='hosts')( terom@26: html.thead( terom@26: html.tr( terom@26: html.th(title) for title in COLUMNS terom@26: ), terom@26: ), terom@26: html.tbody( terom@26: html.tr(id=host.id)( terom@26: html.td(html.a(href=self.url(ItemHandler, id=host.id))('#')), terom@26: ( terom@26: html.td(value) for value in ( terom@26: host.ip, host.mac, host.name, host.gw, host.seen(), terom@26: ) terom@26: ), terom@26: html.td(class_=host.state_class())(host.state), terom@26: ) for host in self.hosts terom@26: ) terom@25: ), terom@25: html.script(type='text/javascript')(""" terom@25: $(document).ready(hosts_realtime({params})); terom@25: """.format(params=params) terom@25: ) terom@25: ) terom@25: