Interfaces detail - Githubissues

Hello, I congratulate you for your great effort and work in the development of this project, but you know that I am new to programming and I would like you to help me so that in the tab show all the interfaces (interface name, status, description and macaddress) for each switch in a new table, but I can't do the insert to the table, it just brings me the last value. Here is my code example added to the file "data_collector.py" :

def getInterfaceDetail(device):

resp = device.send_command("show interfaces")
intdata = resp.genie_parse_output()
for iface in intdata:
    if 'Ethernet' not in iface:
        #print(f'found non-ethernet interface: {iface}')
        continue
    if 'GigabitEthernet0/0' in iface:
        #print(f'found management interface: {iface}')
        continue
    print(f"getInterfaceDetail!!!")
    inport = {}
    inport['iface'] = iface
    inport['oper_status'] = intdata[iface]['oper_status']
    inport['description'] = intdata[iface]['description']
    inport['phys_address'] = intdata[iface]['phys_address']

return inport

Result Intport

{'iface': 'GigabitEthernet1/0/1', 'oper_status': 'down', 'description': 'CONNECTION WITH 1/2/026', 'phys_address': '0064.404c.a701'} {'iface': 'GigabitEthernet1/0/2', 'oper_status': 'down', 'description': 'CONNECTION WITH 1/6/127', 'phys_address': '0064.404c.a702'} {'iface': 'GigabitEthernet1/0/3', 'oper_status': 'up', 'description': 'CONNECTION WITH 1/2/045', 'phys_address': '0064.404c.a703'} {'iface': 'GigabitEthernet1/0/4', 'oper_status': 'up', 'description': 'CONNECTION WITH 1/3/050', 'phys_address': '0064.404c.a704'} {'iface': 'GigabitEthernet1/0/5', 'oper_status': 'down', 'description': 'CONNECTION WITH 1/2/028', 'phys_address': '0064.404c.a705'}

and this is how it looks in my manually added database:

Also I don't know how to update if an interface changes from down to up, or from up to down.

Could you help me please.

Hi there! Happy to help. So the first thing you mentioned is that your new code is only returning the last interface / value. In the new block of code you posted, you're iterating through each interface in intdata - and then at the end returning whatever gets stored in inport. However, it looks like each time you loop, you're re-initializing the inport dictionary - so it's not storing any data between each loop.

Using your example, I modified it below - which will return a full dictionary of every interface & their status:

def getInterfaceDetail(device):
    resp = device.send_command("show interfaces")
    intdata = resp.genie_parse_output()
    inport = {}    
    for iface in intdata:
        if 'Ethernet' not in iface:
            #print(f'found non-ethernet interface: {iface}')
            continue
        if 'GigabitEthernet0/0' in iface:
            #print(f'found management interface: {iface}')
            continue
        print(f"getInterfaceDetail!!!")
        inport[iface] = {}
        inport[iface]['oper_status'] = intdata[iface]['oper_status']
        inport[iface]['description'] = intdata[iface]['description']
        inport[iface]['phys_address'] = intdata[iface]['phys_address']
    return inport

As for updating your DB with up/down changes - you'll need to set the data_collector.py to run on a regular interval. Then with your new code that collects the status of each interface, you would have an initial piece that populates the database with each interface - and each subsequent run would just update the oper_status and description fields.

If you need reference code, I do this a little bit with the initial addDeviceToDB function in the data_collector.py. Before any info is collected from any devices, this initially checks to see if the switch exists in the database. If not, it creates a new placeholder row in the DB by calling addSwitch in the switchdb.py file. Every run after, it uses the updateDB function in the db script to insert changes.

I hope this helps! If you need anything else, please let me know.

Matt

Hi Matt; Thank you very much for the help. However I still can't insert all the interfaces of a switch.

I know I'm doing something wrong, but I can't find my mistake

data_collector.py


import os
import yaml
from scrapli.driver.core import IOSXEDriver, NXOSDriver

import switchdb

def loadDevices():
    """
    Load device inventory from config.yml
    """
    print("Loading devices from config file...")
    with open("config.ymll", 'r') as config:
        devicelist = yaml.full_load(config)
    return devicelist['Devices']

def connectToDevice(deviceconfig):
    """
    Parse device config data & open SSH or  Telnet connection 
    """
    print("Loading device configuration...")
    device = {}
    device['host'] = deviceconfig['address']
    device['auth_username'] = deviceconfig['username']
    device['auth_password'] = deviceconfig['password']
    device['auth_strict_key'] = False
    device['transport'] = 'telnet'
    device['timeout_socket'] = 60
    device['timeout_ops'] = 60
    try:
        device['port'] = deviceconfig['port']
    except KeyError:
        pass
    if deviceconfig['type'] == 'ios-xe':
        conn = IOSXEDriver(**device)
    elif deviceconfig['type'] == 'nx-os':
        conn = NXOSDriver(**device)
    try:
        print(f"Attempting connection to {device['host']}")
        conn.open()
        print(f"Successfully connected to {device['host']}")
    except Exception as e:
        print(f"Failed connection to {device['host']}")
        print("Error message is: %s" % e)
        return None
    return conn

def getInterfaceInfo(device):
    """
     -- FOR IOS-XE DEVICES --
    Issue 'Show interface' command to device
    Return iface(interface number), oper_status(up or down), 
    description(desc of interface), phys_address(macaddress)
    """
    resp = device.send_command("show interfaces")
    intdata = resp.genie_parse_output()
    sysinfo = {}
    for iface in intdata:
        if 'Ethernet' not in iface:
            #print(f'found non-ethernet interface: {iface}')
            continue
        if 'GigabitEthernet0/0' in iface:
            #print(f'found management interface: {iface}')
            continue

        sysinfo[iface] = {}
        sysinfo[iface]['oper_status'] = intdata[iface]['oper_status']
        sysinfo[iface]['description'] = intdata[iface]['description']
        sysinfo[iface]['phys_address'] = intdata[iface]['phys_address']
        #print(inport)
    return sysinfo

def getSystemInfoXE(device):
    """
     -- FOR IOS-XE DEVICES --
     Issue 'Show interface' command to device
    Return iface(interface number), oper_status(up or down), 
    description(desc of interface), phys_address(macaddress)
    """
    resp = device.send_command("show interfaces")
    intdata = resp.genie_parse_output()
    sysinfo = {}
    for iface in intdata:
        if 'Ethernet' not in iface:
            #print(f'found non-ethernet interface: {iface}')
            continue
        if 'GigabitEthernet0/0' in iface:
            #print(f'found management interface: {iface}')
            continue

        sysinfo[iface] = {}
        sysinfo[iface]['oper_status'] = intdata[iface]['oper_status']
        sysinfo[iface]['description'] = intdata[iface]['description']
        sysinfo[iface]['phys_address'] = intdata[iface]['phys_address']
        #print(inport)
    return sysinfo

def getSystemInfoNX(device):
    """
     -- FOR NX-OS DEVICES --
    Issue 'Show interface' command to device
    Return iface(interface number), oper_status(up or down), 
    description(desc of interface), phys_address(macaddress)
    """
    resp = device.send_command("show interface")
    intdata = resp.genie_parse_output()
    inport = {}
    for iface in intdata:
        if 'Ethernet' not in iface:
            #print(f'found non-ethernet interface: {iface}')
            continue
        if 'GigabitEthernet0/0' in iface:
            #print(f'found management interface: {iface}')
            continue

        inport[iface] = {}
        inport[iface]['oper_status'] = intdata[iface]['oper_status']
        inport[iface]['description'] = intdata[iface]['description']
        inport[iface]['phys_address'] = intdata[iface]['phys_address']
        #print(inport)
    return inport

def addDeviceToDB(devicelist):
    """
    Update DB entries switch interfaces from the config file
    """
    print("Opening DB connection...")
    swDB = switchdb.DB()
    # Get a list of current switches in the database
    # Compare between new config file - see what should be added/removed
    curswitches = swDB.getAllSummary()
    currentSwitches = [row[0] for row in curswitches]
    newSwitches = [devicelist[switch]['address'] for switch in devicelist]
    swRemove = set(currentSwitches).difference(newSwitches)
    swAdd = set(newSwitches).difference(currentSwitches)
    # If switches to remove, purge from the database
    if len(swRemove) > 0:
        print(f"Found {len(swRemove)} switches no longer in config file")
        for switchIP in swRemove:
            print(f"Removing switch ({switchIP}) from DB...")
            swDB.deleteSwitch(switchIP)

    print("Adding devices to DB...")
    for switch in devicelist:
        switchIP = devicelist[switch]['address']
        if switchIP in swAdd:
            print(f"Adding switch ({switch} / {switchIP}) to DB...")
            swDB.addSwitch(str(switch), str(switchIP))
        else:
            print(f"Switch ({switch} / {switchIP} ) already in DB. Skipping...")
    swDB.close()

def updateDB(device, ip, sysinfo, portinfo):
    """
    Insert new system & port information
    into the database
    """
    swDB = switchdb.DB()
    print(f"Updating system info for {device} in DB...")
    swDB.updateSysInfo(device, ip, sysinfo)
    print(f"Updating port info for {device} in DB...")
    swDB.updatePorts(device, ip, portinfo)
    swDB.close()

def updateCheckStatus(device, ip, status):
    """
    Update the last_check database field,
    which indicates if the check passed or failed
    """
    swDB = switchdb.DB()
    print(f"Updating check status for {device} to {status}")
    swDB.updateStatus(device, ip, status)
    swDB.close()

def run():
    """
    Primay function to manage device data collection
    """
    # Load all of our devices from config, then add to DB
    devicelist = loadDevices()
    addDeviceToDB(devicelist)
    # Iterate through each device for processing
    for device in devicelist:
        dev = device
        ip = devicelist[device]['address']
        # Open device connection
        devcon = connectToDevice(devicelist[device])
        if devcon:
            try:
                # Query device for system & port info
                if type(devcon) == IOSXEDriver:
                    sysinfo = getSystemInfoXE(devcon)
                if type(devcon) == NXOSDriver:
                    sysinfo = getSystemInfoNX(devcon)
                portinfo = getInterfaceInfo(devcon)
            except Exception as e:
                print(f'ERROR: {e}')
                updateCheckStatus(device, ip, False)
                continue
            # Update database with new info
            updateDB(dev, ip, sysinfo, portinfo)
            # Update if check succeeeded
            updateCheckStatus(dev, ip, True)
        else:
            # Update DB if last check failed
            updateCheckStatus(device, ip, False)

if __name__ == '__main__':
    run()

switchdb.py


from datetime import datetime
import mysql.connector as connector
from sqlite3 import Error

class DB:
    def __init__(self):
        self.openDB()

  def __init__(self):
        self.openDB()
        self.createDB()
        self.initLastUpdate()

    def openDB(self):
        """
        Open SQLlite DB
        """
        self.conn = None
        try:
            self.conn = sqlite3.connect('./sw-util.db')
        except Error as e:
            print(e)

    def createDB(self):
        """
        Create new table to contain switch info & port utilization data
        """
        sw_info_table = """ CREATE TABLE IF NOT EXISTS switches (
            name text NOT NULL,
            serial text DEFAULT "Not Polled Yet",
            model text DEFAULT "N/A",
            sw_ver text DEFAULT "N/A",
            mgmt_ip text NOT NULL PRIMARY KEY,
            last_check boolean DEFAULT False,
            total_port integer DEFAULT 0,
            up_port integer DEFAULT 0,
            down_port integer DEFAULT 0,
            disabled_port integer DEFAULT 0,
            intop10m integer DEFAULT 0,
            intop100m integer DEFAULT 0,
            intop1g integer DEFAULT 0,
            intop10g integer DEFAULT 0,
            intop25g integer DEFAULT 0,
            intop40g integer DEFAULT 0,
            intop100g integer DEFAULT 0,
            intmedcop integer DEFAULT 0,
            intmedsfp integer DEFAULT 0,
            intmedvirt integer DEFAULT 0
        ); """
        last_update_table = """ CREATE TABLE IF NOT EXISTS last_update (
            id integer NOT NULL PRIMARY KEY,
            lastrun text NOT NULL
        ); """
        detail_interface_table = """ CREATE TABLE IF NOT EXISTS interfaces (
            name text NOT NULL,
            mgmt_ip text NOT NULL,
            iface text NOT NULL,
            oper_status text NOT NULL,
            description text NOT NULL,
            phys_addresstext NOT NULL
        ); """
        cur = self.conn.cursor()
        cur.execute(sw_info_table)
        cur.execute(last_update_table)
        cur.execute(detail_interface_table)

    def addSwitch(self, name, mgmt_ip):
        """
        Insert new switch into DB
        """
        sql = """ INSERT INTO interfaces (name, mgmt_ip) values(?, ?); """
        cur = self.conn.cursor()
        try:
            cur.execute(sql, (name, mgmt_ip))
            print(cur)
            self.conn.commit()
        except mysql.errorcode:
            print(f"Switch {name} with IP: {mgmt_ip} already exists in DB.")

    def updateSysInfo(self, name, mgmt_ip, sysinfo):
        """
        Update switch interfaces info:
        interface position, status, description and macaddress
        """
        sql = """ UPDATE interfaces
                  SET 
                  iface = ?,
                  iface = ?,
                  description = ?,
                  phys_address = ?
                  WHERE name = ?
                  AND mgmt_ip = ?;
        """
        cur = self.conn.cursor()
        cur.execute(sql, (sysinfo['iface'],
                          sysinfo['oper_status'],
                          sysinfo['description'],
                          sysinfo['phys_address'],
                            name, mgmt_ip))
        print(cur)
        self.conn.commit()
        return

    def updatePorts(self,  name, mgmt_ip, portinfo):
        """
        Update switch interfaces info:
        interface position, status, description and macaddress
        """
        sql = """ UPDATE interfaces
                  SET
                  iface = ?,
                  oper_status = ?,
                  description = ?,
                  phys_address = ?
                  WHERE name = ?
                  AND mgmt_ip = ?
        """
        cur = self.conn.cursor()
        cur.execute(sql, (portinfo['iface'],
                          portinfo['oper_status'],
                          portinfo['description'],
                          portinfo['phys_address'],
                          name, mgmt_ip))
        print(cur)
        self.conn.commit()
        return

    def getSwitch(self, name, mgmt_ip):
        """
        Retrieve switch information
        """
        sql = """ SELECT * FROM interfaces WHERE name = ? AND mgmt_ip = ?; """
        cur = self.conn.cursor()
        cur.execute(sql, (name, mgmt_ip))
        result = cur.fetchall()
        return result

    def getAllSummary(self):
        """
        Retrieve info from ALL switches in DB.
        """
        sql = """ SELECT iface, oper_status, description, phys_address FROM interfaces; """
        cur = self.conn.cursor()
        cur.execute(sql)
        result = cur.fetchall()
        return result

    def getSwitchDetail(self, oper_status):
        """
        Retrieve info from ALL switches in DB.
        """
        sql = """ SELECT * FROM interfaces WHERE oper_status = ?; """
        cur = self.conn.cursor()
        cur.execute(sql, [oper_status])
        result = cur.fetchall()
        return result

    def updateStatus(self, name, iface, oper_status):
        """
        Update only the oper_status column with
        whether or not the last polling succeeded
        """
        sql = """ UPDATE interfaces  SET oper_status = ? WHERE name = ? and iface = ?; """
        cur = self.conn.cursor()
        cur.execute(sql, (oper_status, name, iface))
        self.conn.commit()
        print("DB Update completed")
        return

    def close(self):
        self.conn.close()

Greetings! Adolph.

Hey there! Okay - so initially I did try to use your examples, but I think some of it's been modified quite a bit already. So I opted to just modify the existing code to add support for what you're looking to do.

Everything has been committed to the main branch now:

data_collector.py will collect additional detail from the switch (physical address, description operational state/speed/duplex)
switchdb.py has been updated with a new table to store this information, as well as the appropriate insert/update/select queries
The frontend web dashboard has also been updated to display this info in a table on the switch detail page.

Here's an example screenshot of what it looks like: https://github.com/0x2142/switchport-web-dashboard/blob/main/screenshots/dashboard-detail-example.PNG

Does this help / accomplish what you were looking for? Please let me know - I'm happy to make a few additional modifications if I can

- Matt

Hi Matt,

Thank you very much for answering me. If I already saw the screenshot, and it's perfect that's what I meant. But I have a problem, copy your new code with the modifications you made in the scripts, but now it does not insert the values in the database, the only thing that inserts is the hostname and ip address

Even so, thank you very much for the prompt response. I will review the scripts on my side so that they are compatible with your development.

this returns from the function for each switch.

Working on interface GigabitEthernet1 / 0/50 Working on interface GigabitEthernet1 / 0/51 ERROR: 'description'

Kind Regards

I think the error shown in description must be because my switches are a bit old: https://www.cisco.com/c/en/us/products/collateral/switches/catalyst-3750-series-switches/eol_c51-696372.html?dtid=osscdc000283

Result: def getSystemInfoXE: {'serial': 'FOC1350Z7YB', 'model': 'WS-C3750G-48PS', 'sw_ver': '12 .2 (35) SE5 '}

Result: def getInterfaceInfo: print(intDetailed)

Working on interface GigabitEthernet1/0/1 {'GigabitEthernet1/0/1': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/4/095', 'phys_addr': '0064.4046.d781', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}} Working on interface GigabitEthernet1/0/2 {'GigabitEthernet1/0/1': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/4/095', 'phys_addr': '0064.4046.d781', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/2': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/1/012', 'phys_addr': '0064.4046.d782', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}} Working on interface GigabitEthernet1/0/3 {'GigabitEthernet1/0/1': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/4/095', 'phys_addr': '0064.4046.d781', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/2': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/1/012', 'phys_addr': '0064.4046.d782', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/3': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/5/102', 'phys_addr': '0064.4046.d783', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}} Working on interface GigabitEthernet1/0/4 {'GigabitEthernet1/0/1': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/4/095', 'phys_addr': '0064.4046.d781', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/2': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/1/012', 'phys_addr': '0064.4046.d782', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/3': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/5/102', 'phys_addr': '0064.4046.d783', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/4': {'oper_status': 'down', 'description': 'CONEXION CON USUARIOS 1/4/083', 'phys_addr': '0064.4046.d784', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}} Working on interface GigabitEthernet1/0/5 {'GigabitEthernet1/0/1': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/4/095', 'phys_addr': '0064.4046.d781', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/2': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/1/012', 'phys_addr': '0064.4046.d782', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/3': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/5/102', 'phys_addr': '0064.4046.d783', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/4': {'oper_status': 'down', 'description': 'CONEXION CON USUARIOS 1/4/083', 'phys_addr': '0064.4046.d784', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}, 'GigabitEthernet1/0/5': {'oper_status': 'up', 'description': 'CONEXION CON USUARIOS 1/4/093', 'phys_addr': '0064.4046.d785', 'oper_speed': '100mb/s', 'oper_duplex': 'full'}}

Thanks for the support...

Hmmmm - Well that's no fun at all! Seems like it runs just fine on other interfaces, so I don't think it's the old switch.

So thinking about it - my test/lab device has descriptions on every port. I did a "no description" on one of my ports, and was able to replicate the error you received.

I made a quick modification to data_collector.py to account for this. Can you please pull the updated version & try again?

Hi Matt.

I already executed your new code, in I could see the lines:

try:
             intDetailed [iface] ["description"] = intdata [iface] ["description"]
         except:
             intDetailed [iface] ["description"] = "N / A"

That execption had not crossed my mind.

Thank you very much for the help, it is what I needed. Really thank you very much. This generates a lot of value for me Greetings Adolph

0x2142 / switchport-web-dashboard

Interfaces detail #1

Result Intport