CISCO DNA Center with Grafana Dashboard

May222021

Post Views: 10,527

A common question from develepore and network engineers is having a Network Operations Center Dashboard for Cisco DNA Center in Grafana. With an overview of network, sites, client health and devices without having to login Cisco DNA Center every time to retrieve data. If you search for it, you will find almost nothing which can fetch, fills up and update data in the Grafana dashboard in automatically way.

I have created a solution for that, and with this solution all tasks are performed automatically. Fetch data first from Cisco DNA Center and saves it as JSON for site_health, network_health, client_health and devices_list. Then extract the desired fields and values and saves as .CSV format, then sending it to the MySQL database that Grafana has access to.

All tasks can be done again and again automatically if you do step 6.

NOTE: Before getting started, you may need to enter the following below values in the MySQL my.cnf file, so that you do not get any error messages during adding data to MySQL, and install jq jnson.
If you have already installed Python, add an alias into ~/.bashrc alias python=’python3′

Find my.cnf by running below command and edit it:

mysql --help | grep "Default options" -A 1

And then add as following below codes:

[mysqld]
local-infile=1
secure_file_priv=""
sql_mode=""

[mysql]
local-infile=1

Then restart MySQL service:

systemctl restart mysql

Install jq json:

sudo apt-get install jq

Let’s getting started!

1. Create dna directory

mkdir /home/dna
cd /home/dna

2.Create Bash Script file database.sh that will creating database and tables automatic

touch database.sh
chmod +x database.sh
nano database.sh

3. Copy bellow codes into it and run it (./database.sh) to start creating database and tables. You only need to run it once, then you can delete it

#!/bin/bash
: '
 * @author [Hawar Koyi]
 * @email [hawar.koy@gmail.com]
 * @email [post@hawar.no] 
 * @create date 2020-09-05 20:18:00
 * @desc [Automation - CISCO DNA Center data into Grafana Dashboard]
'

mysql << EOF
use mysql;
CREATE DATABASE IF NOT EXISTS DNA;
USE DNA;
CREATE TABLE IF NOT EXISTS devices_list (
  lastUpdated varchar(30) DEFAULT NULL,
  upTime varchar(30) DEFAULT NULL,
  collectionStatus varchar(30) DEFAULT NULL,
  hostname varchar(30) DEFAULT NULL,
  macAddress varchar(30) DEFAULT NULL,
  managementIpAddress varchar(30) DEFAULT NULL,
  role varchar(30) DEFAULT NULL,
  platformId varchar(30) DEFAULT NULL,
  softwareVersion varchar(20) DEFAULT NULL,
  datetime datetime DEFAULT CURRENT_TIMESTAMP,
  id INT NOT NULL AUTO_INCREMENT,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS site_health (
    clientHealthWired INT DEFAULT NULL,
    networkHealthAccess INT DEFAULT NULL,
    networkHealthCore INT DEFAULT NULL,
    networkHealthDistribution INT DEFAULT NULL,
    networkHealthRouter INT DEFAULT NULL,
    siteName VARCHAR(255),
    siteType VARCHAR(255),
    clientHealthWireless INT DEFAULT NULL,
    networkHealthOthers INT DEFAULT NULL,
    networkHealthWireless INT DEFAULT NULL,
    datetime datetime DEFAULT CURRENT_TIMESTAMP,
    id INT NOT NULL AUTO_INCREMENT,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS network_health (
  category VARCHAR(20) DEFAULT NULL,
  goodPercentage INT DEFAULT NULL,
  healthScore INT DEFAULT NULL,
  datetime datetime DEFAULT CURRENT_TIMESTAMP,
  id INT NOT NULL AUTO_INCREMENT,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS client_health (
  category VARCHAR(20) DEFAULT NULL,
  scoreDetail INT DEFAULT NULL,
  datetime datetime DEFAULT CURRENT_TIMESTAMP,
  id INT NOT NULL AUTO_INCREMENT,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
EOF

4. Create Bash Script file dna_to_database.sh
This script uses to contact DNA Center and download data as .json, then convert it to .csv and send it to database
All tasks are done automatically by the script, and you only need to run it for the first time, otherwise step 6 do the tasks again for you

touch dna_to_database.sh
chmod +x dna_to_database.sh
nano dna_to_database.sh

5. Copy Bash Script codes and run it (./dna_to_database.sh) , look at step 7, if you want use it in your environment

#!/bin/bash
: '
 * @author [Hawar Koyi]
 * @email [hawar.koy@gmail.com]
 * @email [post@hawar.no] 
 * @create date 2020-09-05 20:38:57 - updated 2021-06-13 10:16:04
 * @desc [Automation - CISCO DNA Center data into Grafana Dashboard]
'

# Authentication
DNA_URL="https://sandboxdnac.cisco.com" #change it 
auth_URL="/dna/system/api/v1/auth/token"
basic_auth="ZGV2bmV0dXNlcjpDaXNjbzEyMyE=" #change it

# URLs
site_health="/dna/intent/api/v1/site-health"
network_health="/dna/intent/api/v1/network-health"
client_health="/dna/intent/api/v1/client-health"
devices_list="/dna/intent/api/v1/network-device"

#DNA directory
DNA_directory="/home/dna/"

#Function to reset CLI color to default white 
default-color()
{
   echo -e "\e[0m" 
}

#Site health function
get_site_health ()
{
    #API to get site_health and save it as site_health.json
    site_health_json=$(curl -k "$DNA_URL$site_health" \
    -X GET -H "x-auth-token: $token" | python -mjson.tool > "$DNA_directory"site_health.json)
    #Save site_health file as csv
    site_health_csv=$(jq -r '.response[] | [.clientHealthWired, .networkHealthAccess, .networkHealthCore, .networkHealthDistribution, .networkHealthRouter, .siteName, .siteType, .clientHealthWireless, .networkHealthOthers, .networkHealthWireless] |  @csv' "$DNA_directory"site_health.json > "$DNA_directory"site_health.csv)
} #End of get_site_health

#Network health function
get_network_health () 
{
    #API to get network_health and save it as network_health.json
    network_health_json=$(curl -k "$DNA_URL$network_health" \
    -X GET -H "x-auth-token: $token" | python -mjson.tool > "$DNA_directory"network_health.json)
    #Save network_health as csv
    network_health_csv=$(jq -r '.healthDistirubution[] | [.category, .goodPercentage, .healthScore] |  @csv' "$DNA_directory"network_health.json > "$DNA_directory"network_health.csv)

} #End of get_network_health

#Client health function
get_client_health () 
{
    #API to get client_health and save it as client_health.json
    client_health_json=$(curl -k "$DNA_URL$client_health" \
    -X GET -H "x-auth-token: $token" | python -mjson.tool > "$DNA_directory"client_health.json)
    #Save client_health as csv
    client_health_csv=$(jq -r '.response[].scoreDetail[] | [.scoreCategory.value, .scoreValue ] |  @csv' "$DNA_directory"client_health.json > "$DNA_directory"client_health.csv)

} #End of get_client_health

#Site health function
get_devices_list ()
{
    #API to get devices_list and save it as devices_list.json
    devices_list_json=$(curl -k "$DNA_URL$devices_list" \
    -X GET -H "x-auth-token: $token" | python -mjson.tool > "$DNA_directory"devices_list.json)
    #Save devices_list file as csv
    devices_list_csv=$(jq -r '.response[] | [.lastUpdated, .upTime, .collectionStatus, .hostname, .macAddress, .managementIpAddress, .role, .platformId, .softwareVersion] |  @csv' "$DNA_directory"devices_list.json > "$DNA_directory"devices_list.csv)
} #End of get_devices_list

#Function to get token, site_health, network_health, client_health and devices_list from Cisco DNA center and 
get_health ()
{
statuscode=$(curl -k -o /dev/null --silent --head --write-out '%{http_code}\n' "$DNA_URL$auth_URL" \
             -X POST -H "Authorization: Basic $basic_auth" )
    if [ "$statuscode" -eq 200 ]; then
        token_url=$(curl -k "$DNA_URL$auth_URL" \
        -X POST -H "Authorization: Basic $basic_auth" )
        #sed to remove first 10 characters and 2 last characters.
        token=$(echo "$token_url" | sed -e 's/^.\{10\}//' -e 's/.\{2\}$//')
        #Call all functions
        get_site_health
        get_network_health
        get_client_health
        get_devices_list
    else
        echo -e "\e[91m\nToken can not be creating, please check URL or Base code\nError HTTP Code is: $statuscode"
        default-color
        exit
    fi
} #End of get_health function
#Call get_health function
get_health

#Database
#Change schema and infile location if needed 
mysql << EOF
use mysql;
use DNA;
-- Truncate site_health table and re-add data again
truncate table site_health;
load data local infile "/home/dna/site_health.csv" 
    into table site_health 
    fields terminated by','
    OPTIONALLY ENCLOSED BY '"'
    lines terminated by'\n'
    (clientHealthWired, networkHealthAccess, networkHealthCore, networkHealthDistribution, networkHealthRouter, siteName, siteType, clientHealthWireless, 
    networkHealthOthers, networkHealthWireless) 
    set datetime = now(), id = null;
-- End of site_health

-- Truncate devices_list table and re-add data again
truncate table devices_list;
load data local infile "/home/dna/devices_list.csv" 
    into table devices_list 
    fields terminated by','
    ENCLOSED BY '"'
    lines terminated by'\n'
    (lastUpdated, upTime, collectionStatus, hostname, macAddress, managementIpAddress, role, platformId, softwareVersion) 
    set datetime = now(), id = null;
-- End of site_health

-- Fills up network_health table with new data
load data local infile "/home/dna/network_health.csv" 
    into table network_health
    fields terminated by','
    OPTIONALLY ENCLOSED BY '"'
    lines terminated by'\n'
    (category, goodPercentage, healthScore)
    set datetime = now(), id = null;
-- End of adding data into network_health table

-- Truncate client_health table and re-add data again
truncate table client_health;
load data local infile "/home/dna/client_health.csv" 
    into table client_health
    fields terminated by','
    OPTIONALLY ENCLOSED BY '"'
    lines terminated by'\n'
    (category, scoreDetail)
    set datetime = now(), id = null;
-- End of client_health
EOF

#End of Bash Script

6. Add dna_to_database.sh to cronjob to get automatic new data (every 15 min) from Cisco DNA Center and send it to database to get Grafana Dashboard updated evenly.

crontab -e
15 * * * * /bin/bash /home/dna/dna_to_database.sh >/dev/null 2>&1

7. Change line 12 and 14 in dna_to_database.sh about your DNA Center environment.
You can still use current details, it’s owned by Cisco Sandbox and try to test it.

DNA_URL="https://sandboxdnac.cisco.com" #change it to your DNA IP
auth_URL="/dna/system/api/v1/auth/token"
basic_auth="ZGV2bmV0dXNlcjpDaXNjbzEyMyE=" #change it

8. Login to Grafana Cloud or Grafana local

9. Add MySQL details inside Grafana datasources as shown in the image below or Check my preview post about it

10. Add my created Grafana Dashboard (12948) and import it into Grafana Dashboard as shown i the image below.

11. To get router health for a building (Tech Center) query it as like the image below.

SELECT datetime AS "time", networkHealthRouter
FROM site_health
WHERE siteName LIKE 'Tech Center'
ORDER BY datetime;

Additional: Watch my below video if you can’t figure out or form YouTube

If you want to have more fields, you can add it as you want from here.

Enjoy it, and like it if you want too Linkedin