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

AnthonyCho

Morning All

So would something like this work?

Sub MerakiUpDownStatsToDb()

Dim conn
Set conn = createobject("Adodb.Connection")
Dim sConnString
Dim SqlStatement
sConnString = "Provider=SQLOLEDB;Data Source=" & SQLServer01 & "; Initial Catalog=" & MerakiDB & "; Integrated Security=SSPI;"

conn.Open sConnString
conn.CommandTimeout = 900

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

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)
<SET API COLUMNS HERE>
Next
SqlStatement = "INSERT INTO [dbo.TheTable] " & _"(Location,DeviceName,UpTime,DownTime) VALUES (Location,DeviceName,UpTime,DownTime)"
conn.Execute(SqlStatement)

End If

AxL1971

what is the StartScript, also the network ID you pass to the API call, where is thar data coming from. I have this in another database and then pass each of my network ID to the subroutine

This is what I do to pass my network ID from a database

sqlConLive=<my sql server connection>
sSQL="SELECT * FROM [MerakiData].[dbo].[MerakiNetworks]"
Set Connection = CreateObject("ADODB.Connection")
set Recordset = CreateObject("ADODB.Recordset")
Connection.Open sqlConLive
Recordset.Open sSQL,Connection
If Recordset.EOF Then
exit sub
Else
Do While NOT Recordset.Eof
rOffice = Recordset("Office")
rNet=Recordset("NetworkID")

< here I call the routine to call the API call with the rnet variable passed to my subroutine>

Recordset.MoveNext
Loop
End If
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing

Otherwise should work only comment is the writing to the database code... have it in a seperate sub routine and pass the required parameters - I have also added some error catching. example below

sub writeSQL(Location,DeviceName,UpTime,DownTime)

On Error Resume Next
Dim conn
Set conn = createobject("Adodb.Connection")
Dim sConnString
Dim SqlStatement
sConnString = "Provider=SQLOLEDB;Data Source=" & SQLServer01 & "; Initial Catalog=" & MerakiDB & "; Integrated Security=SSPI;"

conn.Open sConnString
conn.CommandTimeout = 900

SqlStatement = "INSERT INTO [dbo.TheTable] " & _"(Location,DeviceName,UpTime,DownTime) VALUES (Location,DeviceName,UpTime,DownTime)"
conn.Execute(SqlStatement)
conn.close
Set conn = Nothing

If Err.Number <> 0 Then
WScript.Echo "Error Writing to SQL " & Err.Description
Err.Clear
End If

End Sub

AnthonyCho

Thanks Axl1971

So can we have one subroutine inside the other?

sub SetMerakiUpDownTime (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)

Sub MerakiUpDownStatsToDb()

Dim conn

Set conn = createobject("Adodb.Connection")

Dim sConnString

Dim SqlStatement

sConnString = "Provider=SQLOLEDB;Data Source=" & SQLServer01 & "; Initial Catalog=" & MerakiDB & "; Integrated Security=SSPI;"

conn.Open sConnString

conn.CommandTimeout = 900

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

StartScript

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)

NEXT

call WriteSQL(x,y,z)

sub writeSQL(Location,DeviceName,UpTime,DownTime)

On Error Resume Next

Dim conn

Set conn = createobject("Adodb.Connection")

Dim sConnString

Dim SqlStatement

sConnString = "Provider=SQLOLEDB;Data Source=" & SQLServer01 & "; Initial Catalog=" & MerakiDB & "; Integrated Security=SSPI;"

conn.Open sConnString

conn.CommandTimeout = 900

SqlStatement = "INSERT INTO [dbo.TheTable] " & _"(Location,DeviceName,UpTime,DownTime) VALUES (Location,DeviceName,UpTime,DownTime)"

conn.Execute(SqlStatement)

conn.close

Set conn = Nothing

If Err.Number <> 0 Then

WScript.Echo "Error Writing to SQL " & Err.Description

Err.Clear

End If

End Sub

End If

end sub

AnthonyCho

Or would it be something like

......

For i = 0 to Ubound(apiData)

NEXT

call WriteSQL(Location,DeviceName,UpTime,DownTime)

On Error Resume Next

Dim conn

Set conn = createobject("Adodb.Connection")

Dim sConnString

Dim SqlStatement

sConnString = "Provider=SQLOLEDB;Data Source=" & SQLServer01 & "; Initial Catalog=" & MerakiDB & "; Integrated Security=SSPI;"

conn.Open sConnString

conn.CommandTimeout = 900

SqlStatement = "INSERT INTO [dbo.TheTable] " & _"(Location,DeviceName,UpTime,DownTime) VALUES (Location,DeviceName,UpTime,DownTime)"

conn.Execute(SqlStatement)

conn.close

Set conn = Nothing

If Err.Number <> 0 Then

WScript.Echo "Error Writing to SQL " & Err.Description

Err.Clear

End If

End If

END Sub

AxL1971

you need to create a subroutine called writesql - with the following code .. then you call the subroutine with the paramters

sub WriteSQL(Location,DeviceName,UpTime,DownTime)

On Error Resume Next

Dim conn

Set conn = createobject("Adodb.Connection")

Dim sConnString

Dim SqlStatement

sConnString = "Provider=SQLOLEDB;Data Source=" & SQLServer01 & "; Initial Catalog=" & MerakiDB & "; Integrated Security=SSPI;"

conn.Open sConnString

conn.CommandTimeout = 900

SqlStatement = "INSERT INTO [dbo.TheTable] Location,DeviceName,UpTime,DownTime) VALUES (Location,DeviceName,UpTime,DownTime)"

conn.Execute(SqlStatement)

conn.close

Set conn = Nothing

If Err.Number <> 0 Then

WScript.Echo "Error Writing to SQL " & Err.Description

Err.Clear

End If

end sub

then you call the above

For i = 0 to Ubound(apiData)

< process your data >

call WriteSQL(Location,DeviceName,UpTime,DownTime)

NEXT

May I ask, do you have any software development experience

AxL1971

you create subroutines and call these when you need perfrom some action

as an example what I do is have multiple sub routines, example below

- write to sql database

- read from sql database

- write to a csv/text file

- read from csv/text file

hope that makes sense

Meraki Community

Getting Data out of Meraki into SQL Server

Getting Data out of Meraki into SQL Server