The Meraki GUI is generally pretty helpful and often does provide the following information in one place. However, I want to show how to go about manually doing a mundane data manipulation task to show a process that can be useful for so many other purposes. Namely, using CLI to pull raw data directly from the cloud, clean the data to remove unwanted characters or fields, and then form that data into an array (like CSV or Excel spreadsheet) so that further data manipulation can be done.
Today I want to show a device inventory of company assets. I want to see the name, serial number, LAN IP, and model number of the assets so I could for example add it to a third-party "up/down" monitoring tool.
How to manage this without using the GUI? Simple, use the Meraki API.
First, login into your Meraki dashboard. Go to Organization, then Settings. Check the box to "enable access to the Cisco Meraki Dashboard API". Save the changes and then click on the profile hyperlink.
Inside your profile screen click the box to "Generate new API Key". Copy this key down on a notepad.
Using a Linux server create a variable with the API key value using the following command and your apikey value:
export apikey=41aa26b8fasdfasdfadsfasdfasdfasdfda45f408
Now we need to find our Company Organization ID with the following command:
curl --location --request GET "https://api.meraki.com/api/v1/organizations" \
--header "X-Cisco-Meraki-API-Key: $apikey" | jq .
Now we are ready to create a second variable for Org ID:
export organizationId=6zzzz4
We can now run an API command for "devices/statuses" to do a dump of all devices with all variables: (When pasting commands into a notepad, remove any blank lines).
curl --location --request GET "https://api.meraki.com/api/v1/organizations/$organizationId/devices/statuses" \
--header "X-Cisco-Meraki-API-Key: $apikey" | jq .
The general JSON variables we can now play with are:
"name", "serial", "mac", "publicIp", "networkId", "status", "lastReportedAt",
The access points and switches have additional variables for:
"lanIp", "gateway", "ipType", "primaryDns", "secondaryDns"
Finally, the MX security appliances have the following additional variables:
"usingCellularFailover", "wan1Ip", "wan1Gateway", "wan1IpType", "wan1PrimaryDns", "wan1SecondaryDns", "wan2Ip", "wan2Gateway", "wan2IpType", "wan2PrimaryDns", "wan2SecondaryDns",
I want to see the name, serial number, IP address, and model number of the assets so I am going to craft my command like this:
curl --location --request GET "https://api.meraki.com/api/v1/organizations/$organizationId/devices/statuses" --header "X-Cisco-Meraki-API-Key: $apikey" | jq ".[] | [.name, .serial, .status, .lanIp, .wan1Ip] | @csv" | sed -e 's/"//g' | tr -d '\\'
The JQ command is creating a csv file with the data from the variables .name, .serial, .status, .lanIP, and .wan1Ip. I'm then using "sed" and "tr" to replace the extra \ and " characters.
Now I can copy this into Excel.
Now frustratingly, the Meraki API call doesn't give me all of the data I want in one place. I still would like the model number of the devices. While I'm at it, I might as well grab the MAC and the Network ID. This might come in handy at some point in the future.
The next API command is "inventoryDevices". We will craft the following command to pull the remaining variables from Meraki.
curl --location --request GET "https://api.meraki.com/api/v1/organizations/$organizationId/inventoryDevices" \
--header "X-Cisco-Meraki-API-Key: $apikey" | jq ".[] | [.serial, .mac, .networkId, .model] | @csv" | sed -e 's/"//g' | tr -d '\\'
While this output is great, it doesn't line up with the other spreadsheet list. I have way more devices in this inventoryDevices list that are not being used or were never claimed and I can not just do a "1 for 1" import into the other devices/statuses spreadsheet.
Fine... lets copy the data to a second tab within excel.
This next part gets a little tricky. We will use the "vlookup" function in excel to cross-reference the serial numbers from tab 1, column B to the inventory list on tab 2 using the entire dataset (Sheet2!A1:D132). Then we want to instruct vlookup to return the value of column 3 (mac) and only if the serial numbers are exact matches (TRUE).
For .mac, (F2) =VLOOKUP($B2,Sheet2!$A$1:$D$132,2,TRUE)
For .networkId (G2) =VLOOKUP($B2,Sheet2!$A$1$:D$132,3,TRUE)
For .model (H2) =VLOOKUP($B2,Sheet2!$A$1:$D$132,4,TRUE)
Then copy this to the remaining cells in the Sheet1 inventory to complete the data import.
I know this sounds like a lot but once you get used to the techniques it only takes a few minutes. The pay off is worth it. You will now have a complete, accurate inventory of all devices, with all the important variables, in one place!
-Brett
Reference:
https://documentation.meraki.com/zGeneral_Administration/Other_Topics/The_Cisco_Meraki_Dashboard_API https://stedolan.github.io/jq/manual/