Importing network prefixes from Excel to NetBox.


There comes a time where companies want to move all their excel lists with things to Netbox. The problem is, the excels are huge so we may want to automate that.

First, we need to build a session to Netbox and read in the excel file.

import pandas as pd
import xlrd
import pynetbox
import requests
import pprint
import json
import collections
from collections import OrderedDict

nb = pynetbox.api("https://netbox.acme.com", "<your_netbox_token>")


session = requests.Session()
session.verify = False
nb.http_session = session

df = pd.read_excel('test.xlsx', sheet_name='Cloud-IPs')

Now an important thing in Netbox is that we need to deal with ID’s. Each site has an ID. Each device has an ID. So once we have our sites in netbox, we need to find an ID for this site because we will need that in our import of prefixes.

def get_side_id(location_name):
  sites = nb.dcim.sites.all()

  searched_site = location_name
  for site in sites:
    sitesdict = dict(site)
    if searched_site in sitesdict.values():
      print("Found the site under number " + str(sitesdict.get('id')) + " " + str(sitesdict.get('display')))
      siteid = str(sitesdict.get('id'))
      print(siteid)
    
  return siteid

Now we need to know which rows should be imported. One column in my excel file (with the column title: Location) has the site name. I would like to get a list of rows that have that location so that later we can use this list to import the correct rows:

def get_row_range_from_excel(location_name):
    my_location_name = location_name
    
    if my_location_name == "Mars":
        rows = df.loc[df['Location'] == 'Mars']
    if my_location_name == "Pluton":
        rows = df.loc[df['Location'] == 'Pluton']
    if my_location_name == "Saturn":
        rows = df.loc[df['Location'] == 'Saturn']
    if my_location_name == "Earth":
        rows = df.loc[df['Location'] == 'Earth']
    #I change the type to dictionary because i'm not comfortable working with pandas objects    
    mydict = rows.to_dict()
    #i need another dictionary to reorder things a bit and create one consolidated dictionary
    d = collections.defaultdict(dict)
    for key, value in mydict.items():
     
        for deepkey, deepvalue in value.items():
          
          d[deepkey][key] = deepvalue
    #here i get the row numbers from the keys of the last dictionary     
    ordered_list = []
    for key, value in d.items():
          ordered_list.append(key)
    
    
    return ordered_list

Now it’s time to write up the import function:

def import_azure_excel_to_netbox(location_name):
  task_location_name = location_name
  row_range_list = get_row_range_from_excel(task_location_name)
  

  firstitem = row_range_list[0]        #FIRST ROW TO IMPORT
  lastitem = row_range_list[-1]        #LAST ROW TO IMPORT
 
  siteid = get_side_id(task_location_name) #WE NEED THE SITE ID 
  for i in range (int(firstitem), int(lastitem)):   #get proper range for given location using first and last keys from get_row_range_from_excel(location_name)
    row_dict = dict(df.iloc[i])
    if row_dict['Status'] == 'assigned':  
      #HERE WE NEED TO CREATE VARIABLES FOR THE IMPORT BASED ON CONTENT OF COLUMNS FROM MY EXCEL FILE
      network_comment = row_dict['Comment']
      network_description = row_dict['VNET']
      full_description = str(network_description) + " " + str(network_comment)
      network_role = '1'  #THIS IS THE ID FOR MY NETBOX ROLE: INTRANET. 
      network_prefix = str(row_dict['Network']) + '.' + str(row_dict['Unnamed: 4']) + '.' + str(row_dict['Unnamed: 5']) + '.' + str(row_dict['Unnamed: 6']) + str(row_dict['Mask'])
      mask = str(row_dict['Mask'])
      mask_fixed = mask.lstrip('/')
      
      #I NEED TO MAKE A DECISION WHICH PREFIXES ARE JUST CONTAINERS AND WHICH ARE ACTIVE NETWORKS WITH HOSTS... SOME NICE LOGIC IS NEEDED AND FOR NOW THIS MUST DO BECAUSE I HAVE NO BETTER IDEAS.
      if int(mask_fixed) > 23:
        status = 'active'
      else:
        status = 'container'
      #CHECKING...
      print(network_prefix, network_role, full_description, siteid, status)
      
      try:
        prefix = nb.ipam.prefixes.create(site=siteid, prefix = network_prefix, role= network_role, description= full_description, status= status)
      except pynetbox.RequestError as e:
        print(e.error)

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj /  Zmień )

Zdjęcie na Google

Komentujesz korzystając z konta Google. Wyloguj /  Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj /  Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj /  Zmień )

Połączenie z %s