Source code for netcrawl.io_sql

from functools import wraps
from psycopg2 import errorcodes
import psycopg2, time, traceback
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
from psycopg2.extras import RealDictCursor

from . import config
from .wylog import log, logf, logging
from contextlib import contextmanager


[docs]def useCursor(func): '''Decorator that creates a cursor object to pass to the wrapped method in case one wasn't passed originally. The wrapped function should accept `cur` as an argument.''' @wraps(func) def needsCursor(self, *args, **kwargs): # Check if a cursor was passed if 'cur' in kwargs: return func(self, *args, **kwargs) else: # Create one otherwise with self.conn, self.conn.cursor() as cur: return func(self, *args, **kwargs, cur=cur) return needsCursor
[docs]class sql_logger(): '''Utility class to enable logging and timing SQL execute statements, as well as handling specific errors''' def __init__(self, proc, ignore_duplicates=True): self.proc = proc # If a duplicate constraint is hit, ignore it. self.ignore_duplicates = ignore_duplicates def __enter__(self): log('Beginning execution in [{}]'.format(self.proc), proc=self.proc, v=logging.D) self.start = time.time() def __exit__(self, ty, val, tb): end = time.time() # Ignore the problem if we just added a duplicate if ty is None: log('SQL execution in [{}] completed without error. Duration: [{:.3f}]'.format( self.proc, end - self.start), proc=self.proc, v=logging.D) # Handle duplicate entry violations elif (ty is psycopg2.IntegrityError) and self.ignore_duplicates: if (val.pgcode in (errorcodes.UNIQUE_VIOLATION, errorcodes.NOT_NULL_VIOLATION, )): log('SQL execution in [{}] completed. Null or Unique constraint hit [{}]. Duration: [{:.3f}]'.format( self.proc, val.pgerror, end - self.start), proc=self.proc, v=logging.I) return True else: log('Finished SQL execution in [{}] after [{:.3f}] seconds with [{}] error [{}]. Traceback: [{}]'.format( self.proc, end - self.start, ty.__name__, str(val), traceback.format_tb(tb)), proc=self.proc, v=logging.I)
[docs]class sql_database(): ''' A base class to facilitate SQL database operations. Keyword Args: clean (bool): If True, this causes all database tables to be dropped in order to start with a clean database. .. warning:: Obviously, this is really dangerous. ''' def __init__(self, **kwargs): self.clean = kwargs.get('clean', False)
[docs] def delete_database(self, dbname): '''Deletes a database Returns: bool: True if the database was created Raises: FileExistsError: If the database to be deleted does not exist. IOError: If the database could not be deleted and still exists after execution ''' proc= 'sql_database.delete_database' if not self.database_exists(dbname): log('Database [{}] does not exist'.format(dbname), v=logging.A, proc= proc) raise FileExistsError('Database [{}] does not exist'.format(dbname)) else: log('Database [{}] exists, proceeding to delete'.format(dbname), v=logging.I, proc= proc) with psycopg2.connect(**config.cc.postgres.args) as conn: with conn.cursor() as cur, sql_logger(proc): cur.execute(''' -- Disallow new connections UPDATE pg_database SET datallowconn = 'false' WHERE datname = '{0}'; ALTER DATABASE {0} CONNECTION LIMIT 1; -- Terminate existing connections SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '{0}'; '''.format(dbname)) # Create a new isolated transaction block to drop the database with psycopg2.connect(**config.cc.postgres.args) as conn: conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) with conn.cursor() as cur, sql_logger(proc): cur.execute('DROP DATABASE {0}'.format(dbname)) # Check to make sure it worked if not self.database_exists(dbname): log('Database [{}] deleted'.format(dbname), v=logging.N, proc= proc) return True else: log('Database [{}] could not be deleted'.format(dbname), v=logging.CRITICAL, proc= proc) raise IOError('Database [{}] could not be deleted'.format(dbname))
@logf
[docs] def execute_sql(self, *args, proc=None, fetch= True): ''' Executes a SQL snippet and optionally gets the results Arguments: *args: The arguments to pass along to :meth:`pyscopg2.cursor.execute`. Usually a string containing the SQL statement, and potentially a tuple of parameters. Keyword Args: proc (str): The name of the parent process, for logging purposes fetch (bool): If True, fetches all results from the query Returns: tuple: The results of :meth:`pyscopg2.cursor.fetchall` ''' with self.conn, self.conn.cursor() as cur, sql_logger(proc): cur.execute(*args) if fetch: return cur.fetchall() else: return True
[docs] def execute_sql_gen(self, *args, proc= None): ''' Executes a SQL snippet and gets the results in a generator Arguments: *args: The arguments to pass along to :meth:`pyscopg2.cursor.execute`. Usually a string containing the SQL statement, and potentially a tuple of parameters. Keyword Args: proc (str): The name of the parent process, for logging purposes Returns: generator: The results of :meth:`pyscopg2.cursor.fetchall` ''' with self.conn, self.conn.cursor() as cur, sql_logger(proc): cur.execute(*args) # Iterate over results and yield them in a generator for result in cur: if result is None: return True else: yield result
@useCursor
[docs] def count(self, table, column= '*', value= None, partial_value= None, distinct= False, cur= None): '''Counts the occurrences of the specified `column` in a given `table`. Args: table (str): The table to search in Keyword Args: column (str): The column to count distinct (bool): If True, count only unique matches value (str): If not None, adds a where clause to the count in the format: .. code-block:: sql WHERE column = 'value' partial_value (str): If not None, adds a where clause which will match a partial string in the format: .. code-block:: sql WHERE column like '%partial_value1%' Returns: int: The number of matches ''' proc= 'io.sql.count' # Make the Where statements where= '' if value is not None: assert isinstance(value, str) where+= " WHERE {column} = '{value}' ".format( column= column, value= value) if partial_value: where += ' AND ' if partial_value is not None: assert isinstance(partial_value, str) where+= " WHERE {column} like '%{value}% ".format( column= column, value= partial_value) # Add the distinct qualifier to the statement if distinct: d= ' DISTINCT ' else: d= '' cur.execute( ''' SELECT {distinct} count({column}) FROM {table} {where}; '''.format( distinct= d, column= column, table= table, where= where, )) # Get the result result= cur.fetchone() if result: return int(result[0]) else: return 0
@logf
[docs] def database_exists(self, db): ''' Returns true is the specified database exists Args: db (str): A database name Returns: bool: True if the database exists ''' proc = 'sql_database._database_exists' with psycopg2.connect(**config.cc.postgres.args) as conn: with conn.cursor() as cur, sql_logger(proc): cur.execute("SELECT 1 from pg_database WHERE datname= %s", (db,)) return bool(cur.fetchone())
@logf
[docs] def create_database(self, new_db): ''' Creates a new database Args: new_db (str): Database name to create ''' proc = 'sql_database.create_database' if self.database_exists(new_db): return True else: with psycopg2.connect(**config.cc.postgres.args) as conn: conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) with conn.cursor() as cur, sql_logger(proc): cur.execute('CREATE DATABASE {};'.format(new_db)) return True
[docs] def close(self): '''Closes the connection to the database if it is open''' if not self.conn.closed: self.conn.close()
[docs] def ip_exists(self, ip, table): ''' Check if a given IP exists in the database Args: ip (str): The IP address to check for table (str): The table to check Raises: ValueError: If an argument is an improper type ''' proc = 'sql_database.ip_exists' if ip is None or table is None: raise ValueError(proc + ': IP[{}] or Table[{}] missing'.format( ip, table)) with self.conn, self.conn.cursor() as cur: cur.execute(''' select exists (select * from {t} where ip= %(ip)s limit 1); '''.format(t=table), {'ip': ip} ) return cur.fetchone()[0] # Returns a (False,) tuple
[docs] def ip_name_exists(self, ip, name, table, cur=None): '''Check if a given IP OR Name exists in the database''' proc = 'sql_database.ip_name_exists' if None in (ip, table, name): raise ValueError(proc + ': IP[{}], Name [{}] or Table[{]] missing'.format( ip, name, table)) with self.conn, self.conn.cursor() as cur: cur.execute(''' select exists (select * from %(table)s where ip= %(ip)s OR device_name= %(device)s limit 1);''', {'table': table, 'ip': ip, 'device': name}) return cur.fetchone()[0] # Returns a (False,) tuple
[docs]class main_db(sql_database): def __init__(self, **kwargs): proc = 'main_db.__init__' sql_database.__init__(self, **kwargs) # Get the database name from config self.dbname= config.cc.main.name try: self.create_database(self.dbname) except FileExistsError: pass self.conn = psycopg2.connect(**config.cc.main.args) self.create_table(drop_tables=self.clean) self.ignore_visited = kwargs.get('ignore_visited', True) with self.conn, self.conn.cursor() as cur, sql_logger(proc): # Delete everything in the visited table if self.ignore_visited: cur.execute('DELETE FROM visited') # Then set all pending entries as not working cur.execute(''' UPDATE pending SET working= FALSE ''') def __len__(self): return sql_database.count(self, self.dbname)
[docs] def count_pending(self): '''Counts the number of rows in the table''' return sql_database.count(self, 'pending')
[docs] def count_unique_visited(self): '''Counts the number of unique devices in the database''' with self.conn, self.conn.cursor() as cur: cur.execute(''' SELECT count(distinct device_name) FROM visited;''') return cur.fetchone()[0]
[docs] def remove_pending_record(self, _id): '''Removes a record from the pending table''' proc = 'main_db.remove_processed' # Error checking assert isinstance(_id, int), ( proc + ': _id [{}] is not int'.format(type(_id))) # Delete the processed entry with self.conn, self.conn.cursor() as cur: cur.execute(''' DELETE FROM pending WHERE pending_id = %s ''', (_id,))
[docs] def remove_visited_record(self, ip): '''Removes a record from the pending table''' proc = 'main_db.remove_processed' # Error checking assert isinstance(ip, str), ( proc + ': ip [{}] is not str'.format(type(ip))) # Delete the processed entry with self.conn, self.conn.cursor() as cur: cur.execute(''' DELETE FROM visited WHERE ip = %s ''', (ip,))
[docs] def get_next(self): '''Gets the next pending device. Returns: Dict: The next pending device as a dictionary object with the names of the rows as keys. ''' proc = 'main_db.get_next' # User a special cursor which returns results as dicts with self.conn, self.conn.cursor(cursor_factory=RealDictCursor) as cur: cur.execute(''' SELECT * FROM pending WHERE working= FALSE ORDER BY pending_id ASC LIMIT 1 ''') output = cur.fetchone() # Mark the new entry as being worked on if output: cur.execute(''' UPDATE pending SET working= TRUE WHERE pending_id= %s ''', (output['pending_id'],)) # Return the next device output = dict(output) return output else: return None
[docs] def add_pending_device_d(self, device_d=None, cur=None, **kwargs): proc = 'main_db.add_pending_device_d' # Pending dict template _device_d = { 'device_name': None, 'ip_list': None, 'netmiko_platform': None, 'system_platform': None, 'source_interface': None, 'neighbor_interface': None, 'software': None, 'raw_cdp': None, } # If a dict was supplied, add values from it into the template if device_d: for key, value in device_d.items(): _device_d[key] = value # If the function was passed with keyword args instead elif kwargs: # Template for the seed device for key, value in kwargs.items(): _device_d[key] = value else: return False # Break if no IP address or platform was supplied if ((_device_d['ip_list'] is None) or (len(_device_d['ip_list']) == 0 or (_device_d['netmiko_platform'] is None))): return False with self.conn, self.conn.cursor() as cur: for ip in _device_d['ip_list']: if sql_database.ip_exists(self, ip, 'visited'): log('[{}] already in visited table'.format(ip), v=logging.I, proc=proc) if sql_database.ip_exists(self, ip, 'pending'): log('[{}] already in pending table'.format(ip), v=logging.I, proc=proc) continue with sql_logger(proc): cur.execute(''' INSERT INTO pending ( working, ip, device_name, netmiko_platform, system_platform, source_interface, neighbor_interface, software, raw_cdp ) VALUES (FALSE, %(ip)s, %(device_name)s, %(netmiko_platform)s, %(system_platform)s, %(source_interface)s, %(neighbor_interface)s, %(software)s, %(raw_cdp)s ); ''', { 'ip': ip, 'device_name': _device_d['device_name'], 'netmiko_platform': _device_d['netmiko_platform'], 'system_platform': _device_d['system_platform'], 'source_interface': _device_d['source_interface'], 'neighbor_interface': _device_d['neighbor_interface'], 'software': _device_d['software'], 'raw_cdp': _device_d['raw_cdp'], })
[docs] def add_device_pending_neighbors(self, _device=None, _list=None): """Appends a device or a list of devices to the database Optional Args: _device (network_device): A single device _list (List): List of devices Returns: Boolean: True if write was successful, False otherwise. """ proc = 'main_db.add_device_pending_neighbors' if not _list: _list = [] log('Adding neighbors to pending table', proc=proc, v=logging.N) # If a single device was passed, add it to the list if _device: _list.append(_device) # Return an error if no data was passed if not _list: log('No devices to add', proc=proc, v=logging.A) return False # Process each device in one transaction for device in _list: for neighbor in device.all_neighbors(): if not neighbor.get('netmiko_platform'): log('Neighbor [{}] has no platform. Skipping'.format( neighbor), v=logging.I, proc=proc) continue # Add it to the list of ips to check self.add_pending_device_d(neighbor)
[docs] def create_table(self, drop_tables=True): proc = 'main_db.create_table' log('Creating main.db tables', proc=proc, v=logging.I) with self.conn, self.conn.cursor() as cur: if drop_tables: cur.execute(''' DROP TABLE IF EXISTS pending, visited CASCADE; ''') cur.execute(''' CREATE TABLE IF NOT EXISTS pending( pending_id SERIAL PRIMARY KEY, ip TEXT NOT NULL UNIQUE, working BOOLEAN NOT NULL, device_name TEXT, netmiko_platform TEXT, system_platform TEXT, source_interface TEXT, neighbor_interface TEXT, software TEXT, raw_cdp TEXT, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); CREATE TABLE IF NOT EXISTS visited( visited_id SERIAL PRIMARY KEY, ip TEXT UNIQUE, device_name TEXT, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); ''')
[docs] def add_visited_device_d(self, device_d=None, cur=None, **kwargs): proc = 'main_db.add_visited_device_d' _device_d = { 'device_name': None, 'ip': None, } # If a dict was supplied, add values from it into the template if device_d: for key, value in device_d.items(): _device_d[key] = value # If the function was passed with keyword args instead elif kwargs: # Template for the seed device for key, value in kwargs.items(): _device_d[key] = value else: return False # Break if no IP addres was supplied if _device_d['ip'] is None: raise ValueError(proc + ': No IP or platform was supplied in [{}]'.format(_device_d)) def _execute(_device_d, cur): with sql_logger(proc): cur.execute(''' INSERT INTO visited ( ip, device_name ) VALUES ( %(ip)s, %(device_name)s ); ''', { 'ip': _device_d['ip'], # Must have an IP 'device_name': _device_d['device_name'], }) # Create a cursor if none was passed if cur is None: with self.conn, self.conn.cursor() as cur: return _execute(_device_d, cur) # Otherwise use the passed cursor else: return _execute(_device_d, cur)
[docs] def add_visited_device_nd(self, _device=None, _list=None, cur=None): """Appends a device or a list of devices to the database Optional Args: _device (network_device): A single network_device _list (List): List of network_device objects Returns: Boolean: True if write was successful, False otherwise. """ proc = 'main_db.add_visited_device_nd' if not _list: _list = [] log('Adding device(s) to table.'.format(self.dbname), proc=proc, v=logging.N) # If a single device was passed, add it to the list so that we can # simplify the code later on if _device: _list.append(_device) # Return an error if no data was passed if not _list: log('No devices to add', proc=proc, v=logging.A) return False def _execute(_list, cur): # Process each device for _device in _list: # Get the IP's from the device ip_list = _device.get_ips() log('{} has {} ip(s)'.format(_device.device_name, len(ip_list)), proc=proc, v=logging.I) # For failed devices which couldn't be fully polled: with sql_logger(proc): cur.execute(''' INSERT INTO visited ( device_name, ip ) VALUES (%s, %s); ''', (_device.device_name, _device.ip) ) for ip in ip_list: with sql_logger(proc): cur.execute(''' INSERT INTO visited ( device_name, ip ) VALUES (%s, %s); ''', (_device.device_name, ip) ) # Create a cursor if none was passed if cur is None: with self.conn, self.conn.cursor() as cur: return _execute(_list, cur) # Otherwise use the passed cursor else: return _execute(_list, cur) log('Added {} devices to visited table'.format(len(_list)), proc=proc, v=logging.I) return True
[docs]class device_db(sql_database): def __init__(self, **kwargs): proc = 'device_db.__init__' sql_database.__init__(self, **kwargs) # Get the database name from config self.dbname = config.cc.inventory.name try: self.create_database(self.dbname) except FileExistsError: pass self.conn = psycopg2.connect(**config.cc.inventory.args) self.create_table(drop_tables=self.clean) def __len__(self): 'Returns the number of devices in the database' return sql_database.count(self, 'devices')
[docs] def ip_exists(self, ip): return sql_database.ip_exists(self, ip, 'interfaces')
@useCursor
[docs] def locate_mac(self, mac, cur= None): cur.execute(''' SELECT distinct devices.device_name as device, interface_name as interface, neighbors.device_name as neighbor FROM mac JOIN devices ON mac.device_id=devices.device_id JOIN interfaces on mac.interface_id=interfaces.interface_id LEFT JOIN neighbors on mac.interface_id=neighbors.interface_id WHERE mac_address like %s; ''', ('%' + mac + '%', )) return cur.fetchall()
@useCursor
[docs] def delete_device_record(self, id, cur= None): '''Removes a record from the devices table''' proc = 'device_db.remove_device_record' # Error checking assert isinstance(id, int), ( proc + ': id [{}] is not int'.format(type(id))) # Delete the entry cur.execute(''' DELETE FROM devices WHERE device_id = %s ''', (id, ))
[docs] def devices_on_subnet(self, subnet): with self.conn, self.conn.cursor() as cur: cur.execute(''' SELECT distinct interfaces.device_id FROM interfaces JOIN devices on interfaces.device_id=devices.device_id WHERE network_ip is %s; ''', (subnet, )) results= cur.fetchall() # Return a nicely formatted list of device ID's results= [x[0] for x in results] return sorted(set(results))
[docs] def macs_on_subnet(self, subnet): with self.conn, self.conn.cursor() as cur: cur.execute(''' SELECT distinct mac_address FROM ( SELECT distinct interface_id FROM ( SELECT distinct device_id FROM interfaces WHERE network_ip = %s) as foo JOIN interfaces ON interfaces.device_id=foo.device_id) as bar JOIN mac on mac.interface_id = bar.interface_id; ''', (subnet, )) # Create a generator over the macs so that we don't # get overwhelmed for mac in cur: if mac is None: return True else: yield mac[0]
[docs] def device_macs(self, device_id): with self.conn, self.conn.cursor() as cur: cur.execute(''' SELECT mac_address FROM mac JOIN interfaces on mac.interface_id=interfaces.interface_id WHERE interfaces.device_id = %s; ''', (device_id, )) return cur.fetchall()
#=========================================================================== # def device_id_exists(self, id): # '''Returns True if a given device_id exists''' # # proc = 'io_sql.device_id_exists' # # with self.conn, self.conn.cursor() as cur: # cur.execute(''' # SELECT device_id # FROM devices # WHERE device_id = %s; # ''', # (id, )) # result= cur.fetchone() # # if result is None: return False # else: return result[0] #=========================================================================== #=========================================================================== # def unique_name_exists(self, name): # '''Returns True if a given unique_name already exists''' # proc = 'io_sql.unique_name_exists' # # with self.conn, self.conn.cursor() as cur: # cur.execute(''' # SELECT device_id # FROM devices # WHERE unique_name = %s; # ''', # (name.upper(),)) # result= cur.fetchone() # # if result is None: return False # else: return result[0] #===========================================================================
[docs] def exists(self, device_id= None, unique_name= None, device_name= None, ): ''' Checks whether a device record is present in the devices table. Tries each supplied identifier in order until a match is found, then returns the device_id of the found record. Keyword Args: device_id (int): If not None, check the device_id column for a match unique_name (str): If not None, check the unique_name column for a match device_name (str): If not None, check the device_name column for a match Returns int: The device_id of the first match found bool: False if not found ''' # Error checking if (device_id is None and unique_name is None and device_name is None): raise ValueError('No values passed') with self.conn, self.conn.cursor() as cur: # Try each possible method until a match is found if device_id: cur.execute(''' SELECT device_id FROM devices WHERE device_id = %s limit 1; ''', (device_id, )) result= cur.fetchone() if result: return int(result[0]) if unique_name: cur.execute(''' SELECT device_id FROM devices WHERE unique_name = %s limit 1; ''', (unique_name, )) result= cur.fetchone() if result: return int(result[0]) if device_name: cur.execute(''' SELECT device_id FROM devices WHERE device_name = %s limit 1; ''', (device_name, )) result= cur.fetchone() if result: return int(result[0]) return False
[docs] def add_device_nd(self, _device): """Appends a device to the database Args: _device (network_device): A single network_device Returns: Boolean: False if write was unsuccessful Int: Index of the device that was added, if successful """ proc = 'device_db.add_device_nd' # Return an error if no data was passed if not _device: log('No devices to add', proc=proc, v=logging.A) return False log('Adding device to devices table'.format(self.dbname), proc=proc, v=logging.N) # Do everything in one transaction with self.conn, self.conn.cursor() as cur: device_id = self.insert_device_entry(_device, cur) # Add all the device's serials for serial in _device.serial_numbers: self.insert_serial_entry(device_id, serial, cur) # Add all of the device's interfaces for interf in _device.interfaces: interface_id = self.insert_interface_entry(device_id, interf, cur) # Add all the interface's mac addresses for mac_address in interf.mac_address_table: mac_id = self.insert_mac_entry(device_id, interface_id, mac_address, cur) # Add each neighbor + ip that was matched to an interface for neighbor in interf.neighbors: neighbor_id = self.insert_neighbor_entry(device_id, interface_id, neighbor, cur) for n_ip in neighbor: self.insert_neighbor_ip_entry(neighbor_id, n_ip, cur) # Add each neighbor + ip not matched to an interface for neighbor in _device.neighbors: neighbor_id = self.insert_neighbor_entry(device_id, None, neighbor, cur) for n_ip in neighbor: self.insert_neighbor_ip_entry(neighbor_id, n_ip, cur) self.conn.commit() return device_id
[docs] def get_device_record(self, column, value): '''Get a device record based on a lookup column. 'WHERE column = value' Returns: psycopg2 dict object ''' with self.conn as conn, conn.cursor( cursor_factory=psycopg2.extras.DictCursor) as cur: cur.execute(''' SELECT * FROM devices WHERE {} = %s limit 1; '''.format(column), (value, ) ) return cur.fetchone()
############################################## # Update existing device ##############################################
[docs] def process_duplicate_device(self, device): ''' Parent method for handling an existing device which needs to be updated. 1. Determine if the `device` exists and, if so, get the device_id 2. Overwrite all entries in the device with the new device 3. Set a new updated time for all dependent tables 4. Delete any interfaces and serials which no longer exist 5. Add any new interfaces and serials 6. Add any new MAC addresses 7. Update any newly non-existent MAC addresses Args: device (NetworkDevice): A network device object to check against for duplicates Returns: bool: True if a duplicate was found and updated ''' proc= 'main.process_duplicate_device' # Check if the device exists in the database. If so, get the device_id index = self.exists(unique_name= device.unique_name) if not index: log('Not a duplicate record: [{}]'.format( device.device_name), v=logging.I, proc= proc) return False # Proceed to process the duplicate device log('Positive Duplicate record: [{}]'.format( device.device_name), v=logging.N, proc= proc) self.update_device_entry() return True
@useCursor
[docs] def update_device_entry(self, device, cur= None, device_id= None, unique_name= None ): ''' Overwrites all entries in the Devices table with a matching `device_id` or `unique_name` with the information in `device`. Args: device (NetworkDevice): The device to source updates from Keyword Args: cur (psycopg2.cursor): Cursor object used to update the database device_id (int): If not None, overwrites the row at this index with `device` unique_name (str): If not None, overwrites any row with a matching `unique_name` field with `device`. .. note:: If both `device_id` and `unique_name` are given, the method will update **all** entries that match **either** key. Raises: ValueError: No `unique_name` or `device_id` passed to the method ''' proc= 'device_db.update_device_entry' # Make sure we got a valid id or name if (unique_name is None and device_id is None): log('No unique_name or device_id passed to to the method', proc=proc, v= logging.A) raise ValueError( proc+ ': No unique_name or device_id passed to to the method') # Make the 'where' clause where_clause= ' OR '.join([ '{} = %({})s\n'.format(x, 'w'+x) for x in ('device_id', 'unique_name') if x is not None]) # Update the devices table with the device cur.execute(''' UPDATE devices SET device_name= %(device_name)s, unique_name= %(unique_name)s, netmiko_platform= %(netmiko_platform)s, system_platform= %(system_platform)s, software= %(software)s, raw_cdp= %(raw_cdp)s, config= %(config)s, failed= %(failed)s, error_log= %(error_log)s, processing_error= %(processing_error)s, tcp_22= %(tcp_22)s, tcp_23= %(tcp_23)s, username= %(username)s, password= %(password)s, cred_type= %(cred_type)s, updated= now() WHERE {} '''.format(where_clause), { 'device_name': device.device_name, 'unique_name': device.unique_name, 'netmiko_platform': device.netmiko_platform, 'system_platform': device.system_platform, 'software': device.software, 'raw_cdp': device.raw_cdp, 'config': device.config, 'failed': device.failed, 'error_log': device.error_log, 'processing_error': device.processing_error, 'tcp_22': device.tcp_22, 'tcp_23': device.tcp_23, 'username': device.username, 'password': device.short_pass(), 'cred_type': device.cred_type, # Where 'wdevice_id': device_id, 'wunique_name': unique_name, }) return True
@useCursor
[docs] def set_dependents_as_updated(self, device_id, cur= None): '''Sets the last touched time on all dependents of the given device_id to now''' cur.execute(''' UPDATE devices SET updated = now() WHERE device_id = %(d)s; UPDATE interfaces SET updated = now() WHERE device_id = %(d)s; UPDATE mac SET updated = now() WHERE device_id = %(d)s; UPDATE neighbors SET updated = now() WHERE device_id = %(d)s; UPDATE serials SET updated = now() WHERE device_id = %(d)s; UPDATE neighbor_ips SET updated = now() WHERE neighbor_id = (SELECT neighbor_id FROM neighbors WHERE device_id = %(d)s); ''', {'d': device_id})
###################################################### # Add new device Methods ######################################################
[docs] def insert_device_entry(self, device, cur): # Add the device into the database cur.execute(''' INSERT INTO devices ( device_name, unique_name, netmiko_platform, system_platform, software, raw_cdp, config, failed, error_log, processing_error, tcp_22, tcp_23, username, password, cred_type ) VALUES ( %(device_name)s, %(unique_name)s, %(netmiko_platform)s, %(system_platform)s, %(software)s, %(raw_cdp)s, %(config)s, %(failed)s, %(error_log)s, %(processing_error)s, %(tcp_22)s, %(tcp_23)s, %(username)s, %(password)s, %(cred_type)s ) RETURNING device_id; ''', { 'device_name': device.device_name, 'unique_name': device.unique_name, 'netmiko_platform': device.netmiko_platform, 'system_platform': device.system_platform, 'software': device.software, 'raw_cdp': device.raw_cdp, 'config': device.config, 'failed': device.failed, 'error_log': device.error_log, 'processing_error': device.processing_error, 'tcp_22': device.tcp_22, 'tcp_23': device.tcp_23, 'username': device.username, 'password': device.short_pass(), 'cred_type': device.cred_type, }) device.device_id= cur.fetchone()[0] return device.device_id
[docs] def insert_interface_entry(self, device_id, interf, cur): cur.execute(''' INSERT INTO interfaces ( device_id, interface_name, interface_type, interface_number, ip, subnet, virtual_ip, description, raw_interface, network_ip ) VALUES ( %(device_id)s, %(interface_name)s, %(interface_type)s, %(interface_number)s, %(ip)s, %(subnet)s, %(virtual_ip)s, %(description)s, %(raw_interface)s, %(network_ip)s ) RETURNING interface_id; ''', { 'device_id': device_id, 'interface_name': interf.interface_name, 'interface_type': interf.interface_type, 'interface_number': interf.interface_number, 'ip': interf.interface_ip, 'subnet': interf.interface_subnet, 'virtual_ip': interf.virtual_ip, 'description': interf.interface_description, 'raw_interface': interf.raw_interface, 'network_ip': interf.network_ip, }) return cur.fetchone()[0]
[docs] def insert_serial_entry(self, device_id, serial, cur): cur.execute(''' INSERT INTO serials ( device_id, serialnum, name, description, productid, vendorid ) VALUES ( %(device_id)s, %(serialnum)s, %(name)s, %(description)s, %(productid)s, %(vendorid)s ) RETURNING serial_id; ''', { 'device_id': device_id, 'serialnum': serial.get('serialnum', None), 'name': serial.get('name', None), 'description': serial.get('desc', None), 'productid': serial.get('productid', None), 'vendorid': serial.get('vendorid', None), }) return cur.fetchone()[0]
[docs] def insert_neighbor_ip_entry(self, neighbor_id, ip, cur): cur.execute(''' INSERT INTO neighbor_ips ( neighbor_id, ip ) VALUES ( %(neighbor_id)s, %(ip)s ) RETURNING neighbor_ip_id; ''', { 'neighbor_id': neighbor_id, 'ip': ip, }) return cur.fetchone()[0]
[docs] def insert_neighbor_entry(self, device_id, interface_id, neighbor, cur): cur.execute(''' INSERT INTO neighbors ( device_id, interface_id, device_name, netmiko_platform, system_platform, source_interface, neighbor_interface, software, raw_cdp ) VALUES ( %(device_id)s, %(interface_id)s, %(device_name)s, %(netmiko_platform)s, %(system_platform)s, %(source_interface)s, %(neighbor_interface)s, %(software)s, %(raw_cdp)s ) RETURNING neighbor_id; ''', { 'device_id': device_id, 'interface_id': interface_id, 'device_name': neighbor.get('device_name', None), 'netmiko_platform': neighbor.get('netmiko_platform', None), 'system_platform': neighbor.get('system_platform', None), 'source_interface': neighbor.get('source_interface', None), 'neighbor_interface': neighbor.get('neighbor_interface', None), 'software': neighbor.get('software', None), 'raw_cdp': neighbor.get('raw_cdp', None), }) return cur.fetchone()[0]
[docs] def insert_mac_entry(self, device_id, interface_id, mac_address, cur): cur.execute(''' INSERT INTO mac ( device_id, interface_id, mac_address ) VALUES ( %(device_id)s, %(interface_id)s, %(mac_address)s ) RETURNING mac_id; ''', { 'device_id': device_id, 'interface_id': interface_id, 'mac_address': mac_address, }) return cur.fetchone()[0]
[docs] def create_table(self, drop_tables=True): proc = 'device_db.create_table' with self.conn, self.conn.cursor() as cur: if drop_tables: cur.execute(''' DROP TABLE IF EXISTS neighbor_IPs, mac, serials, neighbors, interfaces, devices CASCADE; ''') cur.execute(''' CREATE TABLE IF NOT EXISTS devices( device_id SERIAL PRIMARY KEY , device_name TEXT, unique_name TEXT, netmiko_platform TEXT, system_platform TEXT, software TEXT, raw_cdp TEXT, config TEXT, failed BOOLEAN, error_log TEXT, processing_error BOOLEAN, tcp_22 BOOLEAN, tcp_23 BOOLEAN, username TEXT, password TEXT, cred_type TEXT, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); CREATE TABLE IF NOT EXISTS Interfaces( interface_id BIGSERIAL PRIMARY KEY , device_id INTEGER NOT NULL, interface_name TEXT NOT NULL, interface_number TEXT, interface_type TEXT, ip TEXT, subnet TEXT, virtual_ip TEXT, description TEXT, raw_interface TEXT, network_ip TEXT, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), FOREIGN KEY(device_id) REFERENCES Devices(device_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS MAC( mac_id BIGSERIAL PRIMARY KEY , device_id INTEGER NOT NULL, interface_id INTEGER NOT NULL, mac_address TEXT NOT NULL, seen_last_scan BOOLEAN DEFAULT TRUE, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), last_seen TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), FOREIGN KEY(interface_id) REFERENCES Interfaces(interface_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(device_id) REFERENCES Devices(device_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS Serials( serial_id SERIAL PRIMARY KEY , device_id INTEGER NOT NULL, serialnum TEXT NOT NULL, name TEXT, description TEXT, productid TEXT, vendorid TEXT, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), FOREIGN KEY(device_id) REFERENCES Devices(device_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS Neighbors( neighbor_id BIGSERIAL PRIMARY KEY , device_id INTEGER NOT NULL, interface_id INTEGER, device_name TEXT, netmiko_platform TEXT, system_platform TEXT, source_interface TEXT, neighbor_interface TEXT, software TEXT, raw_cdp TEXT, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), FOREIGN KEY(device_id) REFERENCES Devices(device_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(interface_id) REFERENCES Interfaces(interface_id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE IF NOT EXISTS Neighbor_IPs( neighbor_ip_id BIGSERIAL PRIMARY KEY , neighbor_id INTEGER NOT NULL, ip TEXT NOT NULL, type TEXT, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), FOREIGN KEY(neighbor_id) REFERENCES Neighbors(neighbor_id) ON DELETE CASCADE ON UPDATE CASCADE ); ''')