Dear friend,
We all like spreadsheets, as this is an easy and intuitive way of representing data for us, humans. The possibility to analyze data with spreadsheets are endless, starting from simple aggregation till complicated pivots are endless. Even the people who say that they don’t like spreadsheets, use them so often. Network automation, for sure, is not an exclusion here.
2
3
4
5
retrieval system, or transmitted in any form or by any
means, electronic, mechanical or photocopying, recording,
or otherwise, for commercial purposes without the
prior permission of the author.
Are Spreadsheets Valid In Automation Era?
They absolutely are. We haven’t seen so far any non-IT person, or even IT high-profile engineer or manager, which won’t like spreadsheets. To put it simple, spreadsheets can be an easy way to represent data collected from network devices; likewise, it can be a good way to provide some input, either as inventory or as configuration details for your network automation intent. So the questions becomes, provided you can read data from your spreadsheet in a programmatic way, what would you do with it?
The good news is that we can teach you all the essential and advanced concepts in Network Automation so that you can unleash the true power of network programmability with spreadsheets (and not only):
We offer the following training programs for you:
- Zero-to-Hero Network Automation Training
- High-scale automation with Nornir
- Ansible Automation Orchestration with Ansble Tower / AWX
- Expert-level training: Closed-loop Automation and Next-generation Monitoring
During these trainings you will learn the following topics:
- Success and failure strategies to build the automation tools.
- Principles of software developments and the most useful and convenient tools.
- Data encoding (free-text, XML, JSON, YAML, Protobuf).
- Model-driven network automation with YANG, NETCONF, RESTCONF, GNMI.
- Full configuration templating with Jinja2 based on the source of truth (NetBox).
- Best programming languages (Python, Bash) for developing automation
- The most rock-solid and functional tools for configuration management (Ansible) and Python-based automation frameworks (Nornir).
- Network automation infrastructure (Linux, Linux networking, KVM, Docker).
- Orchestration of automation workflows with AWX and its integration with NetBox, GitHub, as well as custom execution environments for better scalability.
- Collection network data via SNMP and streaming telemetry with Prometheus
- Building API gateways with Python leveraging Fast API
- Integration of alerting with Slack and your own APIs
- … and many more
Moreover, we put all mentions technologies in the context of real use cases, which our team has solved and are solving in various projects in the service providers, enterprise and data centre networks and systems across the Europe and USA. That gives you opportunity to ask questions to understand the solutions in-depts and have discussions about your own projects. And on top of that, each technology is provided with online demos and labs to master your skills thoroughly. Such a mixture creates a unique learning environment, which all students value so much. Join us and unleash your potential.
Brief Description
We already have said enough today about why spreadsheets are still important and why we create this blogpost. From now on, we’ll focus on how we will do that.
The use case we are to look into is the one that network engineers face almost daily: collect in one place information from multiple data sources. This one place ultimately will be a spreadsheet with multiple sheets.
The way you collect information doesn’t really matter: it can be as legacy as scrapping CLI and parsing the output with regular expressions; or it can be also more future-proof, such as using the NETCONF/YANG or GNMI/YANG. In this blogpot we will use the latter. In fact, we will continue the topic we started the last week about automation of Microsoft Azure OCP SONiC with GNMI. So, let’s put the formal objectives:
- We would like to collect information about the interfaces from Microsoft Azure OCP SONiC.
- We’d like to collect this information in a model-driven way using GNMI.
- We’d like to convert the collected information in a Pandas dataframe so that we can work with it in Python.
- We’d like to store the collected data in a format of Excel spreadsheet with each data set collected being placed on its own sheet.
Lab Setup
We are going to re-use the same topology we have used in the previous blogpost:
It is not mandatory to use SONiC. It just happened we have it already. You are welcome to use Nokia SR OS, Arista EOS, Cisco IOS XR/NX-OS, or any other OS. By the way, you can learn how to automate those platforms in our Zero-to-Hero Network Automation Training.
Let’s a bit remind what we run where:
- Network Automation Host:
- Debian Linux 11 (Bullseye)
- Python 3.9
- pyGNMI 0.8.9
- pandas 1.5.2
- openpyxl 3.0.10
- Network Device:
- Microsoft Azure OCP SONiC
- Emulated platform Dell S6000
- GNMI is enabled and properly working
Enroll to our Network Automation Training to learn how GNMI works and how to use it.
Solution with Python
Our solution will consist of 3 steps:
- Figuring proper GNMI path to poll
- Collect data and convert it in Pandas Data Frame to use in Python
- Write it into a file
In order to be able to make these steps, we’d need the following Python libraries to be installed:
- pygnmi
- pandas
- openpyxl
Do it with on your Linux host:
Step 1. Collect Data via GNMI from SONiC Network Device
For this step we need installed pygnmi library
To collect the data from the network device we are going to use the pyGNMI. If we would run the script we have created in the previous blogpost, we’d see that there is a number of YANG modules supported by SONiC:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
{
"supported_models": [
{
"name": "openconfig-acl",
"organization": "OpenConfig working group",
"version": "1.0.2"
},
{
"name": "openconfig-acl",
"organization": "OpenConfig working group",
"version": "1.0.2"
},
{
"name": "openconfig-interfaces",
"organization": "OpenConfig working group",
"version": "1.0.2"
},
{
"name": "openconfig-lldp",
"organization": "OpenConfig working group",
"version": "1.0.2"
},
{
"name": "openconfig-platform",
"organization": "OpenConfig working group",
"version": "1.0.2"
},
{
"name": "openconfig-system",
"organization": "OpenConfig working group",
"version": "1.0.2"
},
{
"name": "ietf-yang-library",
"organization": "IETF NETCONF (Network Configuration) Working Group",
"version": "2016-06-21"
},
{
"name": "sonic-db",
"organization": "SONiC",
"version": "0.1.0"
}
],
"supported_encodings": [
"json",
"json_ietf"
],
"gnmi_version": "0.7.0"
}
Enroll to our Network Automation Trainings to learn more about YANG and OpenConfig.
Initially we started with OpenConfig/YANG modules. However, either due to the fact we are using the VM or due to some other fact, we were unable to retrieve any data. Probably, we should have re-build the SONiC image; however, as it (how OpenConfig works on Microsoft Azure OCP SONiC) is not the core focus of this blogpost, we decided to look what else we can retrieve. Looking in the output above you can spot that there is some sonic-db YANG module. Looking further, we figured out there is a small number of SONiC-specific YANG modules, which can be used as well. So we tried to collect information from those modules and we actually were able to get something:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# Modules
from pygnmi.client import gNMIclient
import json
# Variables
TARGET = {
"host": "dev-pygnmi-sonic-003",
"port": 50051,
"username": "admin",
"password": "YourPaSsWoRd",
}
# Body
if __name__ == "__main__":
with gNMIclient(
target=(TARGET["host"], TARGET["port"]),
username=TARGET["username"],
password=TARGET["password"],
path_root="certs/ca.pem",
path_cert="certs/server.pem",
path_key="certs/server.key",
) as gc:
path = ["/sonic-interface:sonic-interface/INTERFACE"]
result2 = gc.get(path=path, encoding="json_ietf")
print(json.dumps(result2, indent=4))
Check pyGNMI blogpost to get the basics of pygnmi.
Let’s execute this script:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
{
"notification": [
{
"timestamp": 1673211099162493295,
"prefix": null,
"alias": null,
"atomic": false,
"update": [
{
"path": "sonic-interface/INTERFACE",
"val": {
"sonic-interface:INTERFACE": {
"INTERFACE_IPADDR_LIST": [
{
"ip_prefix": "10.0.0.0/31",
"portname": "Ethernet0"
},
{
"ip_prefix": "10.0.0.50/31",
"portname": "Ethernet100"
},
{
"ip_prefix": "10.0.0.52/31",
"portname": "Ethernet104"
},
{
"ip_prefix": "10.0.0.54/31",
"portname": "Ethernet108"
},
!
! FURTHER OUTPUT IS TRUNCATED FOR BREVITY
Alright, as we don’t have too much information, but we have SOME, which means we can start building our spreadsheet.
Step 2. Build Pandas Data Frame
For this step we need pandas library
Following the official documentation, Data Frame is a 2-dimensional informational instance. 2 dimensions means, that it is, in a nutshell, table or matrix, where we have different tables and rows and we may provide names for either one of dimension (say, only columns or only rows) or to both of them. There are multiple ways how you can provide names for rows (in Pandas it is called “indexes“) or columns (unsurprisingly, they are also called “columns” in Pandas). In our case, as we create Pandas Data Frame from a Python dictionary (to be precise, from the list of identical dictionaries), we can use the name of keys from dictionaries to automatically generate the names of columns for us. Let’s amend our code to create and visualize data frames for us:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
from pygnmi.client import gNMIclient
import pandas
# Variables
TARGET = {
"host": "dev-pygnmi-sonic-003",
"port": 50051,
"username": "admin",
"password": "YourPaSsWoRd",
}
# Body
if __name__ == "__main__":
with gNMIclient(
target=(TARGET["host"], TARGET["port"]),
username=TARGET["username"],
password=TARGET["password"],
path_root="certs/ca.pem",
path_cert="certs/server.pem",
path_key="certs/server.key",
) as gc:
path = ["/sonic-interface:sonic-interface/INTERFACE"]
result2 = gc.get(path=path, encoding="json_ietf")
for path_data in result2["notification"][0]["update"]:
for path_data_key, path_data_val in path_data["val"].items():
sheets = {}
for entry_key, entry_val in path_data_val.items():
target_data = [{"hostname": TARGET["host"], **entry} for entry in entry_val]
sheets[entry_key] = pandas.DataFrame(target_data)
print(entry_key)
print(sheets[entry_key])
Let’s quickly review, what has changed:
- We imported pandas library, which we have installed before.
- We create two for loops to iterate over the GNMI response nicely collected and parsed by pyGNMI (Open Source Python library to manage network devices with GNMI).
- Then, we created dictionary sheets, where we are to store the created Data Frame objects.
- We augment each dictionary item with the hostname of the device, which is needed per our original goal: to store data from multiple network devices; hence, we need a distinguisher.
- Finally, we create for each dictionary from the collected response its own Data Frame and visualize it.
Here what we have as a result of the execution:
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
INTERFACE_IPADDR_LIST
hostname ip_prefix portname
0 dev-pygnmi-sonic-003 10.0.0.0/31 Ethernet0
1 dev-pygnmi-sonic-003 10.0.0.50/31 Ethernet100
2 dev-pygnmi-sonic-003 10.0.0.52/31 Ethernet104
3 dev-pygnmi-sonic-003 10.0.0.54/31 Ethernet108
4 dev-pygnmi-sonic-003 10.0.0.56/31 Ethernet112
5 dev-pygnmi-sonic-003 10.0.0.58/31 Ethernet116
6 dev-pygnmi-sonic-003 10.0.0.6/31 Ethernet12
7 dev-pygnmi-sonic-003 10.0.0.60/31 Ethernet120
8 dev-pygnmi-sonic-003 10.0.0.62/31 Ethernet124
9 dev-pygnmi-sonic-003 10.0.0.8/31 Ethernet16
10 dev-pygnmi-sonic-003 10.0.0.10/31 Ethernet20
11 dev-pygnmi-sonic-003 10.0.0.12/31 Ethernet24
12 dev-pygnmi-sonic-003 10.0.0.14/31 Ethernet28
13 dev-pygnmi-sonic-003 10.0.0.16/31 Ethernet32
14 dev-pygnmi-sonic-003 10.0.0.18/31 Ethernet36
15 dev-pygnmi-sonic-003 10.0.0.2/31 Ethernet4
16 dev-pygnmi-sonic-003 10.0.0.20/31 Ethernet40
17 dev-pygnmi-sonic-003 10.0.0.22/31 Ethernet44
18 dev-pygnmi-sonic-003 10.0.0.24/31 Ethernet48
19 dev-pygnmi-sonic-003 10.0.0.26/31 Ethernet52
20 dev-pygnmi-sonic-003 10.0.0.28/31 Ethernet56
21 dev-pygnmi-sonic-003 10.0.0.30/31 Ethernet60
22 dev-pygnmi-sonic-003 10.0.0.32/31 Ethernet64
23 dev-pygnmi-sonic-003 10.0.0.34/31 Ethernet68
24 dev-pygnmi-sonic-003 10.0.0.36/31 Ethernet72
25 dev-pygnmi-sonic-003 10.0.0.38/31 Ethernet76
26 dev-pygnmi-sonic-003 10.0.0.4/31 Ethernet8
27 dev-pygnmi-sonic-003 10.0.0.40/31 Ethernet80
28 dev-pygnmi-sonic-003 10.0.0.42/31 Ethernet84
29 dev-pygnmi-sonic-003 10.0.0.44/31 Ethernet88
30 dev-pygnmi-sonic-003 10.0.0.46/31 Ethernet92
31 dev-pygnmi-sonic-003 10.0.0.48/31 Ethernet96
INTERFACE_LIST
hostname portname
0 dev-pygnmi-sonic-003 Ethernet0
1 dev-pygnmi-sonic-003 Ethernet100
2 dev-pygnmi-sonic-003 Ethernet104
3 dev-pygnmi-sonic-003 Ethernet108
4 dev-pygnmi-sonic-003 Ethernet112
5 dev-pygnmi-sonic-003 Ethernet116
6 dev-pygnmi-sonic-003 Ethernet12
7 dev-pygnmi-sonic-003 Ethernet120
8 dev-pygnmi-sonic-003 Ethernet124
9 dev-pygnmi-sonic-003 Ethernet16
10 dev-pygnmi-sonic-003 Ethernet20
11 dev-pygnmi-sonic-003 Ethernet24
12 dev-pygnmi-sonic-003 Ethernet28
13 dev-pygnmi-sonic-003 Ethernet32
14 dev-pygnmi-sonic-003 Ethernet36
15 dev-pygnmi-sonic-003 Ethernet4
16 dev-pygnmi-sonic-003 Ethernet40
17 dev-pygnmi-sonic-003 Ethernet44
18 dev-pygnmi-sonic-003 Ethernet48
19 dev-pygnmi-sonic-003 Ethernet52
20 dev-pygnmi-sonic-003 Ethernet56
21 dev-pygnmi-sonic-003 Ethernet60
22 dev-pygnmi-sonic-003 Ethernet64
23 dev-pygnmi-sonic-003 Ethernet68
24 dev-pygnmi-sonic-003 Ethernet72
25 dev-pygnmi-sonic-003 Ethernet76
26 dev-pygnmi-sonic-003 Ethernet8
27 dev-pygnmi-sonic-003 Ethernet80
28 dev-pygnmi-sonic-003 Ethernet84
29 dev-pygnmi-sonic-003 Ethernet88
30 dev-pygnmi-sonic-003 Ethernet92
31 dev-pygnmi-sonic-003 Ethernet96
Despite those 2 Pandas Data Frames are very close to each other, they are coming from different branches in the SONiC YANG module for interfaces. As such, if we happen to collect the data from real network devices, the information may be much more rich.
Step 3. Save Data Frame to Spreadsheet
For this step we need openpyxl library.
The last step in our journey is to write the created data frame into the spreadsheet. To do that, we need to further amend our code.
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import os
from pygnmi.client import gNMIclient
import pandas
# Variables
OUTPUT_DIR = "./output"
TARGET = {
"host": "dev-pygnmi-sonic-003",
"port": 50051,
"username": "admin",
"password": "YourPaSsWoRd",
}
# Body
if __name__ == "__main__":
with gNMIclient(
target=(TARGET["host"], TARGET["port"]),
username=TARGET["username"],
password=TARGET["password"],
path_root="certs/ca.pem",
path_cert="certs/server.pem",
path_key="certs/server.key",
) as gc:
path = ["/sonic-interface:sonic-interface/INTERFACE"]
result2 = gc.get(path=path, encoding="json_ietf")
for path_data in result2["notification"][0]["update"]:
for path_data_key, path_data_val in path_data["val"].items():
if not os.path.exists(OUTPUT_DIR):
os.mkdir(OUTPUT_DIR)
spreadsheet = pandas.ExcelWriter(path=f"{OUTPUT_DIR}/{path_data_key}.xlsx")
sheets = {}
for entry_key, entry_val in path_data_val.items():
target_data = [{"hostname": TARGET["host"], **entry} for entry in entry_val]
sheets[entry_key] = pandas.DataFrame(target_data)
print(entry_key)
print(sheets[entry_key])
sheets[entry_key].to_excel(spreadsheet, sheet_name=entry_key)
spreadsheet.close()
Here is the breakdown of the changes:
- We import os module from the Python’s standard list of modules
- We added new variables OUTPUT_DIR to store path towards directory with the created file.
- Using os.path.exists() function we validate, whether the directory for output is existing and create it, if it is not.
- We create a new object spreadsheet out of ExcelWriter class from pandas, which is responsible for writing the data into the Excel file.
- Call the method to_excel() for each created DataFrame and pass the name of the object with the ExcelWriter and the name of the sheet.
- Once we are done with writing data towards the object, we need to close() it.
Let’s run the script one more time:
2
3
4
5
6
INTERFACE_IPADDR_LIST
hostname ip_prefix portname
0 dev-pygnmi-sonic-003 10.0.0.0/31 Ethernet0
!
! FURTHER OUTPUT IS TRUNCATED FOR BREVITY
Once the execution is completed you will see the new directory created with a file inside:
2
3
4
5
output
`-- sonic-interface:INTERFACE.xlsx
0 directories, 1 file
Let’s open the file with spreadsheet processor you have:
GitHub Repository
Check out the examples from this blogpost in our repository.
Lessons Learned
Actually, when we started writing this blogpost, we thought only about showing you how to easily retrieve the data from the network devices using GNMI and convert that data to a table and save as a spreadsheet. However, we realized that we can do much more that that. So we decided to dig into the topic of the data analysis applied for network automation much deeper and to write a few more blog posts about it.
Summary
We have not yet even scratched the surface of the data analysis with Pandas; however, we did an important step into that direction. Namely, we linked the collection of data from network devices using the model-driven protocol such as GNMI to creation and population of Pandas data frames. As a side-product, we created a multi sheet Excel spreadsheet with the data. We’ll continue on this topic. Take care and good bye!
Need Help? Contract Us
If you need a trusted and experienced partner to automate your network and IT infrastructure, get in touch with us.
P.S.
If you have further questions or you need help with your networks, we are happy to assist you, just send us a message. Also don’t forget to share the article on your social media, if you like it.
BR,
Anton Karneliuk
