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: -
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.
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
Hello and thanks for replying!! Have you got an example of a visual script please?
And of the API calls?
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?
yes all API calls
Thanks, can you give some example scripts of an API call and the corresponding Visual Script? And where did you run these from? Thanks.
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
Thanks!!
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)
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