terom@178: from pvl.verkko import web, db terom@14: from pvl.verkko.utils import parse_timedelta, IPv4Network terom@0: terom@151: from pvl.web import 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@178: ## Model terom@178: import json terom@178: import time terom@178: terom@178: def dt2ts (dt) : terom@178: return int(time.mktime(dt.timetuple())) terom@178: terom@178: def ts2dt (ts) : terom@178: return datetime.datetime.fromtimestamp(ts) terom@178: terom@178: # TODO: this should be DHCPHost terom@0: class Host (object) : terom@0: DATE_FMT = '%Y%m%d' terom@27: TIME_FMT = '%H:%M:%S' 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@179: def network (self) : terom@179: return self.gw terom@179: 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@27: terom@27: @classmethod terom@27: def format_datetime (cls, dt) : terom@27: if (datetime.datetime.now() - dt).days : terom@27: return dt.strftime(cls.DATE_FMT) terom@27: terom@27: else : terom@27: return dt.strftime(cls.TIME_FMT) terom@179: terom@26: def seen (self) : terom@16: return ( terom@27: html.span(title=self.first_seen)(self.format_datetime(self.first_seen)), terom@16: '-', terom@27: html.span(title=self.last_seen)(self.format_datetime(self.last_seen)) 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@178: ## Controller terom@179: def column (attr, title, column, rowhtml=None, sort=True, filter=True, colcss=True, rowfilter=True, rowtitle=None, rowcss=None) : terom@179: """ terom@179: web.Table column spec. terom@179: """ terom@179: terom@179: return (attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss) terom@179: terom@178: class BaseHandler (web.DatabaseHandler) : terom@178: """ terom@178: Common controller stuff for DHCP hosts terom@178: """ terom@26: terom@178: CSS = ( terom@178: "/static/dhcp/hosts.css", terom@178: ) terom@178: JS = ( terom@178: #"/static/jquery/jquery.js" terom@178: ) terom@179: terom@179: TABLE = Host terom@179: TABLE_COLUMNS = ( terom@179: #column('id', "#", Host.id ), terom@179: column('ip', "IP", Host.ip, ), terom@179: column('mac', "MAC", Host.mac, Host.render_mac), terom@179: column('name', "Hostname", Host.name, Host.render_name, rowfilter=False), terom@179: column('gw', "Network", Host.gw, Host.network, rowfilter=False), terom@179: column('seen', "Seen", Host.last_seen, Host.seen, rowfilter=False), terom@179: column('state', "State", Host.count, rowtitle=Host.state_title, rowcss=Host.state_class, rowfilter=False), terom@179: ) terom@179: terom@179: # attr -> column terom@179: TABLE_ATTRS = dict((attr, column) for attr, title, column, sort, filter, colcss, rowhtml, rowfilter, rowtitle, rowcss in TABLE_COLUMNS) terom@179: terom@179: # default sort terom@179: TABLE_SORT = Host.last_seen.desc() terom@179: terom@179: # items per page terom@179: TABLE_PAGE = 10 terom@179: terom@179: # target for items terom@179: TABLE_URL = None terom@179: TABLE_ITEM_URL = None terom@0: terom@5: def query (self) : terom@179: """ terom@179: Database SELECT query. terom@179: """ terom@179: terom@179: return self.db.query(self.TABLE) terom@16: terom@179: def sort (self, query, default=TABLE_SORT) : terom@179: """ terom@179: Apply ?sort= from requset args to query. terom@179: terom@179: Return { attr: sort }, query terom@179: """ terom@179: terom@30: sort = self.request.args.get('sort') terom@0: terom@30: if sort : terom@36: name = sort.lstrip('+-') terom@36: else : terom@36: name = None terom@36: terom@36: if name : terom@179: order_by = self.TABLE_ATTRS[name] terom@3: else : terom@30: order_by = default terom@36: terom@179: # prefix -> ordering terom@36: if not sort : terom@36: pass terom@36: elif sort.startswith('+') : terom@36: order_by = order_by.asc() terom@36: elif sort.startswith('-') : terom@36: order_by = order_by.desc() terom@36: else : terom@36: pass terom@179: terom@179: # apply terom@30: log.debug("sort: %s", order_by) terom@16: terom@179: query = query.order_by(order_by) terom@3: terom@179: return sort, query terom@5: terom@179: def filter_seen (self, value) : terom@179: """ terom@179: Return filter expression for given attr == value terom@179: """ terom@179: terom@179: column = Host.last_seen terom@179: terom@179: if value.isdigit() : terom@179: # specific date terom@179: date = datetime.datetime.strptime(value, Host.DATE_FMT).date() terom@179: terom@179: return db.between(date.strftime(Host.DATE_FMT), terom@179: db.func.strftime(Host.DATE_FMT, Host.first_seen), terom@179: db.func.strftime(Host.DATE_FMT, Host.last_seen) terom@179: ) terom@179: else : terom@179: # recent terom@179: timedelta = parse_timedelta(value) terom@179: terom@179: return ((db.func.now() - Host.last_seen) < timedelta) terom@179: terom@179: # XXX: for sqlite, pgsql should handle this natively? terom@179: # to seconds terom@179: #timeout = timedelta.days * (24 * 60 * 60) + timedelta.seconds terom@179: terom@179: # WHERE strftime('%s', 'now') - strftime('%s', last_seen) < :timeout terom@179: #filter = (db.func.strftime('%s', 'now') - db.func.strftime('%s', Host.last_seen) < timeout) terom@179: terom@179: def filter_ip (self, value) : terom@179: column = Host.ip terom@179: terom@179: # column is IPv4 string literal format... terom@179: if '/' in value : terom@179: return (db.func.inet(Host.ip).op('<<')(db.func.cidr(value))) terom@179: else : terom@179: return (db.func.inet(Host.ip) == db.func.inet(value)) terom@179: terom@179: def filter_mac (self, value) : terom@179: return self.filter_attr('mac', Host.normalize_mac(value)) terom@179: terom@30: def filter_attr (self, attr, value) : terom@30: """ terom@30: Return filter expression for given attr == value terom@30: """ terom@30: terom@179: # preprocess terom@179: like = False terom@30: terom@179: if value.endswith('*') : terom@179: like = value.replace('*', '%') terom@30: terom@179: # filter terom@179: column = self.TABLE_ATTRS[attr] terom@30: terom@179: if like : terom@179: return (column.like(like)) terom@30: else : terom@179: return (column == value) terom@179: terom@179: def _filter (self, attr, values) : terom@179: """ terom@179: Apply filters for given attr -> (value, expression) terom@179: """ terom@30: terom@179: for value in values : terom@179: value = value.strip() terom@179: terom@179: # ignore empty fields terom@179: if not value : terom@179: continue terom@30: terom@179: # lookup attr-specific filter terom@179: filter = getattr(self, 'filter_{attr}'.format(attr=attr), None) terom@179: terom@179: if filter : terom@179: filter = filter(value) terom@30: else : terom@179: # use generic terom@179: filter = self.filter_attr(attr, value) terom@179: terom@179: log.debug("%s: %s: %s", attr, value, filter) terom@179: terom@179: yield value, filter terom@30: terom@179: def filter (self, query) : terom@30: """ terom@30: Apply filters from request.args against given hosts. terom@30: terom@30: Returns (filters, hosts). terom@30: """ terom@30: terom@30: # filter? terom@30: filters = {} terom@30: terom@179: for attr in self.TABLE_ATTRS : terom@179: # from request args terom@179: values = self.request.args.getlist(attr) terom@179: terom@179: # lookup attr filters as expressions terom@179: value_filters = list(self._filter(attr, values)) terom@30: terom@41: # ignore empty fields terom@179: if not value_filters : terom@30: continue terom@179: terom@179: # filtering values, and filter expressions terom@179: values, expressions = zip(*value_filters) terom@30: terom@179: # apply terom@179: query = query.filter(db.or_(*expressions)) terom@30: filters[attr] = values terom@30: terom@179: return filters, query terom@30: terom@30: def filters_title (self) : terom@30: """ terom@30: Return a string representing the applied filters. terom@30: """ terom@30: terom@30: return ', '.join(value for values in self.filters.itervalues() for value in values) terom@30: terom@179: def render_table (self, query, caption=None, sort=None, filters=None, page=None, hilight=None) : terom@179: """ terom@179: Return