"""
Track active DHCP hosts on network by dhcp messages.
"""
import logging; log = logging.getLogger('pvl.dhcp.hosts')
# XXX: from db.dhcp_leases instead?
import pvl.verkko.db as db
class DHCPHostsDatabase (object) :
"""
pvl.verkko.Database dhcp_hosts model for updates.
"""
def __init__ (self, db) :
self.db = db
def create (self) :
"""
CREATE TABLEs
"""
log.info("Creating database tables: dhcp_hosts")
db.dhcp_hosts.create(self.db.engine, checkfirst=True)
def select (self, distinct=(db.dhcp_hosts.c.gw, db.dhcp_hosts.c.ip), interval=None) :
"""
SELECT unique gw/ip hosts, for given interval.
"""
query = db.select(distinct, distinct=True)
if interval :
# timedelta
query = query.where(db.func.now() - db.dhcp_hosts.c.last_seen < interval)
return self.db.select(query)
def insert (self, attrs) :
"""
INSERT new host
"""
query = db.dhcp_hosts.insert().values(
ip = attrs['ip'],
mac = attrs['mac'],
gw = attrs['gw'],
first_seen = attrs['timestamp'],
count = 1,
last_seen = attrs['timestamp'],
state = attrs['state'],
name = attrs.get('name'),
error = attrs.get('error'),
)
# -> id
return self.db.insert(query)
def update (self, attrs) :
"""
UPDATE existing host, or return False if not found.
"""
table = db.dhcp_hosts
query = table.update()
query = query.where((table.c.ip == attrs['ip']) & (table.c.mac == attrs['mac']) & (table.c.gw == attrs['gw']))
query = query.values(
count = db.func.coalesce(table.c.count, 0) + 1,
# set
last_seen = attrs['timestamp'],
state = attrs['state'],
)
if 'name' in attrs :
query = query.values(name = attrs['name'])
if 'error' in attrs :
query = query.values(error = attrs['error'])
# any matched rows?
return self.db.update(query)
def __call__ (self, item) :
"""
Process given DHCP syslog message to update the hosts table.
"""
attrs = {}
# ignore unless we have enough info to fully identify the client
# this means that we omit DHCPDISCOVER messages, but we get the OFFER/REQUEST/ACK
if any(name not in item for name in ('lease', 'hwaddr', 'gateway')) :
# ignore; we require these
return
# do not override error from request on NAK; clear otherwise
# TODO: DHCPINFORM from 192.168.x.y with error -> rogue dhcp?
if item.get('type') == 'DHCPNAK' :
pass
else :
attrs['error'] = item.get('error-type') or item.get('error')
# do not override name unless known
if item.get('name') :
attrs['name'] = item.get('name')
# db: syslog
ATTR_MAP = (
('ip', 'lease'),
('mac', 'hwaddr'),
('gw', 'gateway'),
('timestamp', 'timestamp'),
('state', 'type'),
)
# generic attrs
for key, name in ATTR_MAP :
attrs[key] = item.get(name)
# update existing?
if self.update(attrs) :
log.info("Update: %s", attrs)
else :
# new
log.info("Insert: %s", attrs)
self.insert(attrs)