Meraki Community

AnthonyCho

Good Morning to the Meraki Community

I am new to Meraki so I need some help. I've been tasked to get data out of Meraki into a SQL Server Database so I could do with some help with this. Our team have suggested that an API is the best way to do this; so can we: -

Can we create an API using Python?
Use the API to extract data into a flat file (e.g.) csv?
Format the name of the flat file and send it to an email address?

If this is possible then we have the other tools/knowledge to get the data into SQL Server.

The data we are currently wanting is List of Devices, No. of failures, device failure times, No. Of successes; device success times

Devices can be switches, access points, laptops etc. Devices are located in several sites etc.

AxL1971

I have done this by writing multiple API calls to download the data, parse it and directly write it to a back end SQL database with a basic front end web internal website to show the data.

Most of the API calls are to have a live wireless usage for people within the company who dont have access to the dashboard. With the data in a database I also have a Power BI report that can graphs how many people are in the office over a timeframe.

I wrote all this in a simple visual script

AnthonyCho

Hello and thanks for replying!! Have you got an example of a visual script please?

AnthonyCho

And of the API calls?

AnthonyCho

Have you got an example of an API script and corresponding Visual Script?

Where did you run the API from (was it Python)?

What did you use to run the visual script?

AxL1971

yes all API calls

AnthonyCho

Thanks, can you give some example scripts of an API call and the corresponding Visual Script? And where did you run these from? Thanks.

AxL1971

I will get some example code over, I run these on a windows server as a scheduled task . Obviously servers needs access to the Meraki API URL

AnthonyCho

Thanks!!

double_virgule

Sounds like @AxL1971 has you taken care of, but if you want to try with Python, Meraki has a number of example scripts on their Github distro, including this script that gets all organization devices. You could likely update this or edit it as needed.

https://github.com/meraki/dashboard-api-python/blob/main/examples/org_wide_clients_v1.py

They also have a number of automation scripts:

https://github.com/meraki/automation-scripts

I also have this script I use to export networks and devices to CSV. It's nothing fancy, but you can modify it.

import meraki
import pandas as pd
import os.path 

dashboard = meraki.DashboardAPI("", suppress_logging=True)

organization_id = ''

orgDev = dashboard.organizations.getOrganizationNetworks(organizationId=organization_id,total_pages=1,perPage=5)

df = pd.DataFrame(orgDev)

df.to_csv("C:\\Data\\Outputs\\OrgNetworks.csv")

total = len(df['name'])
print(total)
count = 0
for d in orgDev: 
    count += 1
    print(f"Device {count} of {total}")
    devices = dashboard.networks.getNetworkDevices(d['id'])
    df = pd.DataFrame(devices)
    if os.path.isfile("C:\\Data\\Outputs\\NetworkDevices.csv") == True: 
        df.to_csv("C:\\Data\\Outputs\\NetworkDevices.csv", mode='a', index=False, header=False)
    else: 
        df.to_csv("C:\\Data\\Outputs\\NetworkDevices.csv", index=False)
    print(devices)

AxL1971

This is some example code I have written in vbscript

I have all my network ID's in a database so when I run my API I pass this network ID to the subroutine.

xAPIKey is my API key defined at the start.

I check the HTTP status is 200 so I know the URL was successfull.

After the data is put into an array, I parse this array and to extract the data I want and finally I call the routine to write to the SQL database.

If I wanted to write to a CSV file, I would call a routine to write to a text file as CSV format. Obviously there is more code to what is actually done with parsing the data

Sub DotheWork (sNet)
sUrl = "https://api.meraki.com/api/v1/networks/" & sNet & "/clients?timespan=300&recentDeviceConnections[]=Wireless&statuses[]=Online&perPage=2000"

set http=createObject("Microsoft.XMLHTTP")
http.open "GET",sUrl,false
http.setRequestHeader "Content-Type","application/json"
http.setRequestHeader "Accept","application/json"
http.setRequestHeader "X-Cisco-Meraki-API-Key",xAPIKey
http.send
responseText=http.responseText
If http.Status = 200 Then
apiData = Split(responseText , ",")

For i = 0 to Ubound(apiData)

< this is the where I parse the data to extract the information I need>

Next
call WriteSQL(x,y,z)
End If
end sub

Meraki Community

Getting Data out of Meraki into SQL Server

Getting Data out of Meraki into SQL Server