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
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
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
Or would it be something like
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)
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