Meraki Dashboard Reporting: How can I get a csv of All Clients on ALL networks in an organization?

Willie_McDonald
Getting noticed

Meraki Dashboard Reporting: How can I get a csv of All Clients on ALL networks in an organization?

I'm administrating a decent size Meraki Organization, it currently has ~650 networks with every network consisting of a single MX65 and 1-3 MR33's.

 

Is there a Meraki Dashboard report that I can run that will give me a listing of all Clients connected to all networks in that organization? I'd like it to be in an .csv and sorted by Network name.

 

I think I can probably do it via API, but it'd be nicer if I could use something that already exists.

 

Thanks!

11 REPLIES 11
jdsilva
Kind of a big deal

There isn't anything that I know of to do this in the Dashboard. API is your friend here, though even that route means you have to get a list of clients per device, so you're making a call to get every device in the org, then a call for each device to get the clients. 

Hmm that was what I was afraid of.

 

I already wrote a script that cycles through the 650 networks and checks for new Meraki hardware at each site. If new hardware is found, it gets a few tags added as well as gets a physical address (and moves the pin on the map) from a external csv file.

 

So doing this won't be too much different, a lot of the logic is already in place.

 

Thanks for the prompt response.


@Willie_McDonald wrote:

Hmm that was what I was afraid of.

 

I already wrote a script that cycles through the 650 networks and checks for new Meraki hardware at each site. If new hardware is found, it gets a few tags added as well as gets a physical address (and moves the pin on the map) from a external csv file.

 


You can get all devices in an Org with a single call instead of going through each network:

 

https://api.meraki.com/api_docs#return-the-inventory-for-an-organization

 

Not sure if that'll work for you, but it DRAMATICALLY reduces the time on my scripts to iterate through my Orgs to get individual devices. 

@Cmiller putting all APs into a single network *might* be fine for your deployment, but could also go against a common best practice, but again it depends on your deployment.  Each wireless network is meant to be more of a single site or physical location, like a single branch office, or a corporate office building or group of buildings, or a single school (or perhaps a middle + high school on a common campus, or a single college or university campus for example.  The point is that each of these is a single contiguous AutoRF domain for which all of the APs make their RF decisions about channels and power.

 

I usually use the example of a K12 school district as an example, you would not want to have a single district-wide wireless network across every school. That would be a single large AutoRF domain for buildings that are miles apart, way out of RF range from one another, but they all have to run the same instance of an AutoRF algorithm.  You could potentially have APs in a high school making RF adjustments based off of changes that stemmed from a different school miles away.  That doesn't tend to happen, and RF adjustments tend to remain localized, but it's possible, and inefficient since the majority of the computations involving other schools are unnecessary algorithmic noise.

 

I've seen K12 districts run district-wide wireless networks just fine, but it's definitely less than ideal, and the best practice is to divide up the RF domains into a network-per-site so dozens or hundreds or even thousands of APs don't all have to run the same instance of an algorithm when they're way out of range anyway.

 

Even in the university campus example I gave above, some decide to do a network-per building approach, like for large dorm buildings, versus a single campus-wide network, while others have separate campus-wide networks broken out for dorms, academic buildings, and general outdoor and common areas, while still others do have a single campus-wide deployment (less common).  Just rules of thumb, lots of variables and of course every deployment's different.

 

The switch network is another story, there it might be more common to have a campus or district-wide switching network but with wireless networks broken out site by site.  Only thing there is you wouldn't have site-by-site combined MS+MR networks in Dashboard, likely not a big deal, but it depends.

 

Thanks for all the input.

 

I ended up writing a Python script to get this info. See the code below. You just need to add your API Key in the "api_key" constant, as well as the Organization ID of the organization you're querying in the "org_id" constant.

 

I'm sure this could be optimized better, but I'm a little rusty!

 

Enjoy

 

///////////////////// B E G I N    C O D E //////////////////////////////

# Import "meraki" to allow Meraki function calls
from meraki import meraki

# Import "datetime" to get current date and time
import datetime

# ///////// Start Definition of Script Constants ////////////////
# api_key = Unique API key generated on a per Meraki User basis
# org_id = Unique organization ID of organization you want to query

api_key = '##################'
org_id = '##################'


# Get the current list of networks from your Organization
current_networks = meraki.getnetworklist(api_key, org_id)


# Establish when "now" is, used to create the file name for output
now = datetime.datetime.now()

# Create/Name the output file with year, month, day and hour minutes
filename = now.strftime("%Y-%m-%d %H.%M") + ".txt"

# Open the file for output
f = open(filename,"w+")

# "i" will be used to increment through each "network" in the Organization
i = 0

for i in range(len(current_networks)):

    # Output to console the Name and ID of the current network
    print(current_networks[i]['name'])
    print(current_networks[i]['id'])

    curr_net_id = current_networks[i]['id']
    curr_net_devices = meraki.getnetworkdevices(api_key,curr_net_id)

    # Write to the output file the 'name' of the network and add a comma
    f.write(current_networks[i]['name'])
    f.write(",")

    # Checks for Meraki devices on this network, if there are no devices output to file "No Devices Present"
    if len(curr_net_devices) == 0:
        f.write("No devices Present")
        f.write("\n")

    # "j" will be used to increment through each "device" on this network
    j = 0

    for j in range(len(curr_net_devices)):
        # Output to console the serial number of the device
        print (curr_net_devices[j]['serial'])
        curr_clients = meraki.getclients(api_key,curr_net_devices[j]['serial'])


        # Checks for clients attached to this device, if no clients present output the name, model and serial of the
        # device as well as the message "No Clients"
        if len(curr_clients) == 0:
            # Output to console "No Clients" message
            print ("No clients")

            # Output to file "name", "model", "serial" of the device as well as "No Clients" message
            f.write(current_networks[i]['name'])
            f.write(',')
            f.write(curr_net_devices[j]['model'])
            f.write(',')
            f.write(curr_net_devices[j]['serial'])
            f.write(",")
            f.write("No Clients")
            f.write("\n")

        # "k" will be used to increment the number of clients on each device
        k = 0

        for k in range(len(curr_clients)):
            # Output to console "description" and "mac" of the client
            print (curr_clients[k]['description'],',',curr_clients[k]['mac'])

            # Output to file "name", "model", "serial" of the device as well as the "description" and "mac"
            # of the client
            f.write(current_networks[i]['name'])
            f.write(',')
            f.write(curr_net_devices[j]['model'])
            f.write(',')
            f.write(str(curr_net_devices[j]['name']))
            f.write(',')
            f.write(curr_net_devices[j]['serial'])
            f.write(',')
            f.write(str(curr_clients[k]['description']))
            f.write(',')
            f.write(curr_clients[k]['mac'])
            f.write("\n")

            # Increment "k" to move onto next client on current device
            k = k + 1

        # Increment "j" to move onto next device on current Network
        j = j + 1
    # Increment "i" to move onto next network in Organization
    i = i + 1

# Close the output file
f.close()

///////////////////// E  N  D    C O D E //////////////////////////////////

Hello, I am interested in getting the os, lastseen and address but it does not recognize the keys can you point me in the right direction.

 

# Import "meraki" to allow Meraki function calls
from meraki import meraki


# Import "datetime" to get current date and time
import datetime

# ///////// Start Definition of Script Constants ////////////////
# api_key = Unique API key generated on a per Meraki User basis
# org_id = Unique organization ID of organization you want to query

api_key =
org_id =


# Get the current list of networks from your Organization
current_networks = meraki.getnetworklist(api_key, org_id)


# Establish when "now" is, used to create the file name for output
now = datetime.datetime.now()

# Create/Name the output file with year, month, day and hour minutes
filename = now.strftime("%Y-%m-%d %H.%M") + ".txt"

# Open the file for output
f = open(filename,"w+")

# "i" will be used to increment through each "network" in the Organization
i = 0

f.write("Network Name")
f.write(',')
f.write("Device Model")
f.write(',')
f.write("Device Name")
f.write(',')
f.write("Device serial")
f.write(',')
f.write("Client Description")
f.write(",")
f.write("Client Mac")
f.write(',')
f.write("Client IP")
f.write(',')
f.write("Client vlan")
f.write(',')
f.write("Client seen")
f.write(',')
f.write("Client os")
f.write(',')
f.write("Client switchport")
f.write(',')
f.write("Client address")
f.write("\n")

for i in range(len(current_networks)):

# Output to console the Name and ID of the current network
print(current_networks[i]['name'])
print(current_networks[i]['id'])

curr_net_id = current_networks[i]['id']
curr_net_devices = meraki.getnetworkdevices(api_key,curr_net_id)

# Write to the output file the 'name' of the network and add a comma
#f.write(current_networks[i]['name'])
#f.write(',')

# Checks for Meraki devices on this network, if there are no devices output to file "No Devices Present"
if len(curr_net_devices) == 0:
f.write("No devices Present")
f.write("\n")

# "j" will be used to increment through each "device" on this network
j = 0

for j in range(len(curr_net_devices)):
# Output to console the serial number of the device
print (curr_net_devices[j]['serial'])
curr_clients = meraki.getclients(api_key,curr_net_devices[j]['serial'])


# Checks for clients attached to this device, if no clients present output the name, model and serial of the
# device as well as the message "No Clients"
if len(curr_clients) == 0:
# Output to console "No Clients" message
print ("No clients")

# Output to file "name", "model", "serial" of the device as well as "No Clients" message
f.write(current_networks[i]['name'])
f.write(',')
f.write(curr_net_devices[j]['model'])
f.write(',')
f.write("No Device Name")
f.write(',')
f.write(curr_net_devices[j]['serial'])
f.write(',')
f.write("No Description")
f.write(",")
f.write("No Clients")
f.write(',')
f.write("No ip")
f.write(',')
f.write("No vlan")
f.write(',')
f.write("Not seen")
f.write(',')
f.write("No os")
f.write(',')
f.write("No switchport")
f.write(',')
f.write("No Address")
f.write("\n")

# "k" will be used to increment the number of clients on each device
k = 0

for k in range(len(curr_clients)):
# Output to console "description" and "mac" of the client
print (curr_clients[k]['description'],',',curr_clients[k]['mac'])

# Output to file "name", "model", "serial" of the device as well as the "description" and "mac"
# of the client
f.write(current_networks[i]['name'])
f.write(',')
f.write(curr_net_devices[j]['model'])
f.write(',')
f.write(str(curr_net_devices[j]['name']))
f.write(',')
f.write(curr_net_devices[j]['serial'])
f.write(',')
f.write(str(curr_clients[k]['description']))
f.write(',')
f.write(curr_clients[k]['mac'])
f.write(',')
try:
f.write(curr_clients[k]['ip'])
except:
f.write("No ip info")
f.write(',')
try:
f.write(str(curr_clients[k]['vlan']))
except:
f.write("No vlan info")
f.write(',')
try:
f.write(curr_clients[k]['lastSeen'])
except:
f.write("No lastseen info")
f.write(',')
try:
f.write(curr_clients[k]['os'])
except:
f.write("No OS info")
f.write(',')
try:
f.write(curr_clients[k]['switchport'])
except:
f.write("No switchport info")
f.write(',')
try:
f.write(curr_clients[k]['address'])
except:
f.write("No address info")
f.write("\n")

# Increment "k" to move onto next client on current device
k = k + 1

# Increment "j" to move onto next device on current Network
j = j + 1
# Increment "i" to move onto next network in Organization
i = i + 1
#remove two lines use only for testing
f.close()
exit()


# Close the output file
f.close()

I hate to be the one to resurrect a dead post, but I stumbled upon this the other day and took me till now to try it out, as I am new to Python and the Meraki API, but I tried to run this script as is (after adding in my meraki API key and Org ID), it errors out this message shortly after running it (See attached image).Capture.PNG

 If a client device doesn't have a name for whatever reason, would I need to add something like:

    # Write to the output file the 'name' of the network and add a comma, if a device doesn't have a name output to file "No Name"
    if len(curr_net_name) == 0:
        f.write(current_networks[i]['name'])
        f.write("\n")

Not sure if that's even the right syntax, but something like that is what I'm guessing is what I'll need for it to continue on?

For another topic I took a look at this script too. While I didn't experience your error @Scriptguru1701 I did experience another, namely if you have MV cameras in the network, the getclients throws an error because it's not relevant (cameras have no clients...).

 

I adapted the code to get around this:

 

# Import "meraki" to allow Meraki function calls
import meraki

# Import "datetime" to get current date and time
import datetime

# ///////// Start Definition of Script Constants ////////////////
# api_key = Unique API key generated on a per Meraki User basis
# org_id = Unique organization ID of organization you want to query

api_key = '#################'
org_id = '#############'


# Get the current list of networks from your Organization
current_networks = meraki.getnetworklist(api_key, org_id)


# Establish when "now" is, used to create the file name for output
now = datetime.datetime.now()

# Create/Name the output file with year, month, day and hour minutes
filename = now.strftime("%Y-%m-%d %H.%M") + ".txt"

# Open the file for output
f = open(filename,"w+")

# "i" will be used to increment through each "network" in the Organization
i = 0

for i in range(len(current_networks)):

    # Output to console the Name and ID of the current network
    print("Network Name:" + current_networks[i]['name'])
    print("Network ID:" + current_networks[i]['id'])

    curr_net_id = current_networks[i]['id']
    curr_net_devices = meraki.getnetworkdevices(api_key,curr_net_id)

    # Write to the output file the 'name' of the network and add a comma
    f.write(current_networks[i]['name'])
    f.write(",")

    # Checks for Meraki devices on this network, if there are no devices output to file "No Devices Present"
    if len(curr_net_devices) == 0:
        f.write("No devices Present")
        f.write("\n")

    # "j" will be used to increment through each "device" on this network
    j = 0

    for j in range(len(curr_net_devices)):
        # Output to console the serial number of the device
        print ("Device Serial:" +curr_net_devices[j]['serial'])
        curr_clients = meraki.getclients(api_key,curr_net_devices[j]['serial'])


        # Checks for clients attached to this device, if no clients present output the name, model and serial of the
        # device as well as the message "No Clients". Also catch the error that comes up in case this is a device for
        # which clients are not relevant, e.g. MV camera's.
        if len(curr_clients) == 0 or curr_clients[0] == "Invalid device type":
            # Output to console "No Clients" message
            print ("No clients")

            # Output to file "name", "model", "serial" of the device as well as "No Clients" message
            f.write(current_networks[i]['name'])
            f.write(',')
            f.write(curr_net_devices[j]['model'])
            f.write(',')
            f.write(curr_net_devices[j]['serial'])
            f.write(",")
            f.write("No Clients")
            f.write("\n")

        # Only list the clients if there are any.
        else:
            # "k" will be used to increment the number of clients on each device
            k = 0

            for k in range(len(curr_clients)):
                # Output to console "description" and "mac" of the client
                print (curr_clients[k]['description'],',',curr_clients[k]['mac'])

                # Output to file "name", "model", "serial" of the device as well as the "description" and "mac"
                # of the client
                f.write(current_networks[i]['name'])
                f.write(',')
                f.write(curr_net_devices[j]['model'])
                f.write(',')
                f.write(str(curr_net_devices[j]['name']))
                f.write(',')
                f.write(curr_net_devices[j]['serial'])
                f.write(',')
                f.write(str(curr_clients[k]['description']))
                f.write(',')
                f.write(curr_clients[k]['mac'])
                f.write("\n")

                # Increment "k" to move onto next client on current device
                k = k + 1

        # Increment "j" to move onto next device on current Network
        j = j + 1
    # Increment "i" to move onto next network in Organization
    i = i + 1

# Close the output file
f.close()

 

 

Note that I'm also using the newer version of the libary, so the import statement is just import meraki for me. Hope that helps.

 

Thanks for your work @Willie_McDonald .

Thank you for your assistance @BrechtSchamp and @Willie_McDonald for the original script.  Here is the solution that for whatever reason worked for me: https://community.meraki.com/t5/Dashboard-Administration/Gather-all-Clients-across-an-organization-i... in case someone else has the same issue.

Cmiller
Building a reputation

Not the best option but we put all of out APs and Switches in their own Network so we could do just that, see all the devices connected across our whole network.
SteveWeidner
Here to help

I stumbled across this thread while looking for a solution to a similar need.  For the sake of anyone reading this in the future, I wanted to share another solution that I found.

 

...from the official Meraki github account...

https://github.com/meraki/dashboard-api-python/blob/main/examples/aio_org_wide_clients_v1.py

 

This script iterates through every Org to which your API key has access and through every network in each Org, then it saves a CSV of the clients in each network and finally, compiles all of the client CSVs into one file containing all the clients in all the networks for that organization.

 

I hope this helps!

Get notified when there are additional replies to this discussion.