#!/usr/bin/env python import psycopg def clean(): conn=psycopg.connect("dbname=netdisco password=netdisco user=netdisco host=netdisco") curs=conn.cursor() cleaned = 0 # number cleaned curs.execute(""" SELECT n.switch,n.port,COUNT(n.mac) FROM node n LEFT JOIN node_ip i ON n.mac=i.mac WHERE port LIKE 'FastEthernet0/%' AND i.ip IS NULL GROUP BY n.switch,n.port HAVING COUNT(n.mac) > 40 ORDER BY count DESC """) nodes = curs.dictfetchall() delquery=""" DELETE FROM node WHERE switch=%s AND port=%s AND mac NOT IN (SELECT n.mac FROM node n, node_ip i WHERE n.mac=i.mac AND n.switch=%s AND n.port=%s) """ for x in nodes: s, p, c = x['switch'],x['port'], x['count'] print s, p, c #debug curs.execute(delquery, (s, p, s, p)) cleaned += c conn.commit() return cleaned if __name__ == "__main__": num = clean() print "%d Nodes cleaned" % num