[python]Record router information with mongodb

Introduction

This is a lab demonstration on how to record cisco router’s information to mongo database. Mongo database is very easy to learn for new developers who understand the json structure because the structure to insert data into the database is very json-like this is very easy to learn without knowing SQL syntax.

I am using an ubuntu server 20.04 to host the mongodb, follow the installation process here.

Eve-ng is used as a lab platform to operate the cisco routers.

The lab is simple, create database in mongodb to store the credentials of the routers from an excel sheet, to make parsing easy I use the pandas module to convert the data in excel sheet into dictionary.

The structure of the dictionary object returned by pandas is:

• Outer key uses the column names of the excel sheet.
• Inner key uses the row index, starting from 0.

I need to do some processing with the dictionary object returned by pandas in order to be consumed by netmiko, at minimum netmiko needs these keys: ip, device_type, username and password, so the processed dictionary object has to have these.

Modules for this lab

• os module – To use the os.walk() method to get my excel sheet and return the absolute path of the excel file.
• pathlib module – To get the home directory.
• netmiko module – Perhaps the de-facto standard module for network devices.
• pandas module – For easy excel data manipulation into dictionary.
• pymongo module – For easy mongo database interaction.
  

Create mongodb database

It is very easy to create a database, just state a key name of the mongodb object then the database is created.

from pymongo import MongoClient
mongo_url = "mongodb://192.168.1.245:27017"
dbclient = MongoClient(mongo_url)
db = dbclient["network"]

This script snippet creates a database known as “network”, the database object is then used to create collections (tables in db jargon).

Create collection

device_tbl = db["devices"]

This script snippet creates a table/collection, the object is then used to do CRUD.

Read data from excel sheet

Before knowing pandas, I used to use openpyxl module to parse excel worksheet and convert into dictionary which have lines of code, but pandas reduces the lines of code significantly as a lot of processing has already been done.

from pandas import ExcelFile
import os
from pathlib import Path

for root, dir, file in os.walk(Path.home()):
    if "lab.xlsx" in file:
        xls_path = os.path.join(root, "lab.xlsx")
xls = ExcelFile(xls_path)
df = xls.parse(xls.sheet_names[0])
device_info = df.to_dict()

device_info object is the dictionary parsed from the excel sheet which has this content:

I am using os.walk() to help me find my lab.xlsx file instead of typing out the absolute path.

Process pandas’ dictionary object

I need to do processing of the dictionary returned by pandas so that netmiko can consume it. So this is the script to remove the inner key of the dictionary.

# The number of rows from df
for i in range(len(df)):
    # Remove the row number from inner key.
    for k, v in device_info.items():
        tmp_dict.update(
            {
                k: v[i]
            }
        )
    devices.append(tmp_dict)
    # always clear the dict in order to append new dict.
    tmp_dict = {}

len(df) returns the number of rows that have data, k is the key the iteration will go through ip, device_type, username, password, each key has a dictionary with row index as key and value as the actual data; I need to remove the row index, running the script snippet will produce this:

Insert multiple device config into mongodb

On previous snippet collection object – “devices” is created. The object is used to do CRUD. I need to insert the list of dictionary into devices collection in network database.

device_tbl.insert_many(devices)

The devices object is the processed dictionary object of pandas, this devices object is inserted into mongodb by using insert_many method which accepts a list of dictionary.

Query R1 from mongodb

The device_tbl is a generator and hence I need to iterate the generator to obtain my query result.

for d in device_tbl.find({"ip": "192.168.1.215"}, {"_id": 0}):
    r1_config = d.copy()

The {“_id”: 0} is to exclude “id” from the query result. I am using a new instance which is a copy of d, this is to ensure my original data is not corrupted if there is a scripting mistake, dictionary is mutable.. and it contains the reference of the data… so… by assigning d into another instance is to assign reference and not the actual data if there is an accidental modification in the new instance d’s data will be affected.

Obtain interface info from R1

from netmiko import ConnectHandler

with ConnectHandler(**r1_config) as r1:
    intf_data = r1.send_command("show ip int brief", use_textfsm=True)

Thanks to pre-made ntc-template by setting use_textfsm=True the intf_data is in list of dictionary.

Clone the ntc-template to your home directory, not to your venv or anywhere, only home directory, netmiko will pick the template up.

Create new collection to store R1’s interface info.

I would need a new collection – “R1” – just to store interface information unique only to R1.

Previous snippet I have created a db object, I use the db object to create a new collection – “R1” – and with this new collection object I insert the data obtain from R1.

r1_tbl = db["R1"]
r1_tbl.insert_many(intf_data)