Automation 20. GNMI to Spreadsheet via Pandas or Intro to Data Analysis in Network Automation

Anton Karneliuk

2 years ago

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.


1
2
3
4
5
No part of this blogpost could be reproduced, stored in a 

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:

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.

Start your automation training today.

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:


1
$ pip install pygnmi pandas openpyxl

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:


1
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
$ python sonic_test_pygnmi.py 

{

    "supported_models": &#91;

        {

            "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": &#91;

        "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:


1
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
$ cat sonic_gnmi_to_spreadsheet.py

# 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&#91;"host"], TARGET&#91;"port"]),

        username=TARGET&#91;"username"],

        password=TARGET&#91;"password"],

        path_root="certs/ca.pem",

        path_cert="certs/server.pem",

        path_key="certs/server.key",

    ) as gc:

        path = &#91;"/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:


1
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
$ python sonic_gnmi_to_spreadsheet.py 

{

    "notification": &#91;

        {

            "timestamp": 1673211099162493295,

            "prefix": null,

            "alias": null,

            "atomic": false,

            "update": &#91;

                {

                    "path": "sonic-interface/INTERFACE",

                    "val": {

                        "sonic-interface:INTERFACE": {

                            "INTERFACE_IPADDR_LIST": &#91;

                                {

                                    "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:


1
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
# Modules

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&#91;"host"], TARGET&#91;"port"]),

        username=TARGET&#91;"username"],

        password=TARGET&#91;"password"],

        path_root="certs/ca.pem",

        path_cert="certs/server.pem",

        path_key="certs/server.key",

    ) as gc:

        path = &#91;"/sonic-interface:sonic-interface/INTERFACE"]

        result2 = gc.get(path=path, encoding="json_ietf")



        for path_data in result2&#91;"notification"]&#91;0]&#91;"update"]:

            for path_data_key, path_data_val in path_data&#91;"val"].items():

                sheets = {}



                for entry_key, entry_val in path_data_val.items():

                    target_data = &#91;{"hostname": TARGET&#91;"host"], **entry} for entry in entry_val]

                    sheets&#91;entry_key] = pandas.DataFrame(target_data)



                    print(entry_key)

                    print(sheets&#91;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:


1
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
$ python sonic_gnmi_to_spreadsheet.py 

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.


1
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
# Modules

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&#91;"host"], TARGET&#91;"port"]),

        username=TARGET&#91;"username"],

        password=TARGET&#91;"password"],

        path_root="certs/ca.pem",

        path_cert="certs/server.pem",

        path_key="certs/server.key",

    ) as gc:

        path = &#91;"/sonic-interface:sonic-interface/INTERFACE"]

        result2 = gc.get(path=path, encoding="json_ietf")



        for path_data in result2&#91;"notification"]&#91;0]&#91;"update"]:

            for path_data_key, path_data_val in path_data&#91;"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 = &#91;{"hostname": TARGET&#91;"host"], **entry} for entry in entry_val]

                    sheets&#91;entry_key] = pandas.DataFrame(target_data)



                    print(entry_key)

                    print(sheets&#91;entry_key])



                    sheets&#91;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:


1
2
3
4
5
6
$ python sonic_gnmi_to_spreadsheet.py 

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:


1
2
3
4
5
$ tree output

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