Making architectural decision on a simple DCIM ETL application

Hello, blog!

This week at work, I've been working on PHP-IPAM [1] to NetBox [2] migration. These are Data Center Information Management (DCIM) systems that helps us keep a track of assets at our various sites.

We use an Excel spreadsheet (per site) with the deployment information such as cage locations, racks, rack units to place the devices in, networking connections, and other configuration information. This information is then passed on to the on-site personnel to make it easy for them to setup the site.

Our team members would manually enter the data from the spreadsheet into the IPAM software. I was confident that we could replace this manual process with some Python automation code to read the spreadsheet and then update NetBox automatically.

Step 1 - Scoping the project

There's a Charles Kettering quote:
A problem well-stated is a problem half-solved.

What we needed our code to do could be cleanly split into 3 parts:

  1. (Extract) Parse information from the deployment spreadsheet
  2. Transform the data into relevant fields to fill up in NetBox
  3. (Load) POST data onto NetBox using the API

So, now this looks like an ETL problem. The core problem is reading the human-readable data from the spreadsheet into NetBox ids.

Step 2 - Implementing the project

The JSON file to be posted to NetBox to create devices looks like the following:

{

      "name": "compute-119.tr2-pr-rh",

      "device_type": 365,

      "role": 4,

      "site": 38,

      "location": 34,

      "rack": 187,

      "position": 15,

      "face": "front",

      "airflow": "front-to-rear",

      "status": "active"

}

As you can see; most of the fields have 'ids' but the deployment spreadsheet would have values. e.g. device type - server, role - compute, site - TR2, location - cage 002:112:039, etc.

So, in order to POST device to NetBox; we'd first need to get these IDs. Let's explore the 3 strategies to do this:

1. Naïve per-row lookups

For each row of data in the deployment sheet, we can make multiple calls to NetBox. The advantage of this approach is that it's super simple to reason about but it would be slow for large spreadsheets and increases the number of requests made to NetBox.

2. Preload reference data into in-memory dictionaries (best for our use case & choosen)

In this method, before processing the spreadsheet; we load all relevant reference tables once, then resolve everything in-memory. Using dictionaries in Python is O(1) so it'll be fast. Also, we reduce the number of API calls made to NetBox (DRY - we're not asking for the same information multiple times). The downside of this is obviously the added complexity of startup logic and also we need clear rules for matching (for e.g. the site on the spreadsheet could be named tr2-site but in Netbox, the site may be stored as 'TR2' so we need some 'fuzzy-matching' rules)

3. Maintaining a metadata cache / mapping DB

We can introduce a small DB/table that tracks mapping. A small "metadata service" / table to sync with NetBox (using a cron job) and then, our job reads from this cache, not from NetBox directly. This involves managing the service/db though so was not selected. If in the future, we raise to Hyperscaler levels where we're adding servers daily; then I will formalize the metadata cache. I'd setup a periodic sync.

So, in the end I ended up building the a batch import service that preloads reference data from Netbox into in-memory dictionaries. Then, it reads the deployment sheet row-by-row, creates the JSON payload shown above, & then upload it to NetBox using POST API call.

Thanks for reading! Feel free to let me know if there was any better way of doing this that I hadn't considered.


References:

[1] https://phpipam.net/

[2] https://netboxlabs.com/

Comments

Post a Comment

Popular posts from this blog

Working with APIs using Python

Image
I love automation and believe automating repetitive tasks makes the world a better place as it would free up humans to work on more interesting challenges! APIs are one of the most useful tools in setting up automation so I thought I'd take some time to write about them and share my experience using them. Let's start by defining the word 'Program'. 'Program' can be used as a noun or a verb, in the noun form, it means a set of instructions provided to a computer to perform a certain task while in the verb form it means to provide a human/computer a set of instructions to perform a task automatically. Figure 1: API/Program Illustration API stands for Application Programming Interface and it is a way for humans and programs to interact with programs. Here's a simple illustration of how an API works: Figure 2: API Analogy What this means is that the application has some endpoint that you can reach (such as an HTTP GET request to a particular website endpoint) th...
Read more

RAID! No, not that one

Image
Hello, blog! Been a while. I rejoined Fortinet on the 12th of May with the FortiStack team and it's my second week at the company and already having a great time learning new things. So, the cloud team at Fortinet had mostly worked with Dell servers but now they're trying out MSI servers. The particular server that I'm evaluating for production is an MSI S1205-01-10G . One of the main tasks in setting up a new server is setting up the storage RAID. RAID stands for Redundant Array of Independent Disks and based on the setting writes data to multiple disks for redundancy. That way; if one of the drive fails, the data is still safe. RAID can be setup either with a physical RAID card or by software RAID. The server that we ordered did not have the physical RAID card. This means setting up the virtual RAID. Fig. 1 - MSI S1205-01 server The first 2 drives of the server were populated with 2X 480G SATA SSDs. Fig. 2 - Populated drives on the server This is what the drive looks like...
Read more

Sorting Algorithms

Image
Sorting In one of the previous posts, we have discussed ' Binary search ' in some detail. One of the requirements to run a Binary Search is that the data in our Array is sorted. In this blog, let's take a look at different ways to sort the lists and find the best way to sort the list. 1. Bubble sort Figure [1] Bubble Sort Here's the code for bubble sorting a simple array: def bubble_sort(array):     n = len(array)     for i in range(n):         already_sorted = True         for j in range(n-i-1):             if array[j] > array[j+1]:                 array[j], array[j+1] = array[j+1], array[j]             already_sorted = False         if already_sorted:             break     return array print(bubble_sort([2,3,9,8,7,1])) Since the bubble sort algorithm has 2 for loops...
Read more