diff -r 5fc4c5e83b72 -r 6f339a8a87dc pvl/dhcp/hosts.py
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pvl/dhcp/hosts.py	Sat Jan 26 11:49:16 2013 +0200
@@ -0,0 +1,121 @@
+"""
+    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 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
+        if item.get('type') == 'DHCPNAK' :
+            pass
+        else :
+            attrs['error'] = 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)
+
+