Getting Data out of Meraki into SQL Server

[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)

[AnthonyCho]

Morning; the VS script is getting overly complicated; Is this a Python Script?  Also if I was to run this several times would it overwrite the existing file?  if so I can use this to grab the data then run another process to insert the data into SQL Server

[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

[AnthonyCho]

Morning Everyone

going off the posts above I've knocked this together

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

Sub GetMerakiLocationDeviceNameUpTimeDownTime (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)
<SET THE MERAKI API COLUMNS HERE> EXAMPLE PLEASE......
Next
call WriteSQL(Location,DeviceName,UpTime,DownTime)
End If
end sub

If I could have an example of grabbing the Meraki data that would be greatly appreciated

[AxL1971]

only question is where are you getting your network ID when passing to the API URL call

[AnthonyCho]

Sorry?....

[AxL1971]

some API calls require you to pass the network ID - if you read the API calls documentation this states all the paramteres to pass to the API calls.

https://developer.cisco.com/meraki/api-v1/