Meraki

Tbells · ‎Nov 19 2019

I am trying to get words in a [ ] to be generated in separate words. I have a list of tags in a [] which the python sees as an array which is no.

Examples are shown below

"tags": [

"FileZilla",

"London",

"NUC"

],

"tags": [

"Laptop",

"Stockholm"

],

"tags": [

"Corporate_Apps",

"Jabber",

"London",

"Okta",

"VBDevice",

"exchage2",

"iPhone",

"mobile"

],

Above are the tags for each device in the organisation. I will the tags to come as

Corporate_Apps,

Jabber,

London,

Okta,

VBDevice,

exchage2,

iPhone,

mobile

for each tag as am trying to export it into a SQL table other columns work perfectly but just tags seem not to work while generating it from the API using python into a database. I have tried using split but that doesn't seem to work.

However am really need to Python, please be nice.

Thank you all.

BrechtSchamp · ‎Nov 19 2019

How are you calling the API? I ask because generally when I've seen tags be returned, they've been a single string with tags separated by a space.

In that case to turn them into an array you can simply do:

array_of_tags = your_string_containing_the_tags.split(' ')

It may help if you share some of your code (take out any sensitive information).

Tbells · ‎Nov 19 2019

@BrechtSchamp Thanks for your response, really appreciate

import json

import pandas as pd

import sqlalchemy

import pyodbc

#file = open('rhapitest.json')

import requests

url = "###########/"

querystring = {"fields":["phoneNumber","location","lastConnected"]}

headers = {
'x-cisco-meraki-api-key': "##########",
'content-type': "application/json",
'accept': "application/json",
'cache-control': "no-cache",
'postman-token': "###########"
}

response = requests.request("GET", url, headers=headers, params=querystring)
data = json.loads(response.text)
#print(data)

id,name,tags,osName,systemModel,serialNumber,lastConnected,location = [],[],[],[],[],[],[],[]
#x = 0
for device in data['devices']:
# x = x+1
# print (x)
id.append(device[u'id'])

name.append(device[u'name'])

temp1=(device[u'tags'])

if not temp1:
tags.append("No tags available")

else:
', '.join(temp1)
print(temp1)
# try:
# mylist = (device[u'tags'])
# except:
# tags.append("No tag available")
# else:
# str(mylist).strip('[]')

osName.append(device[u'osName'])

systemModel.append(device[u'systemModel'])

serialNumber.append(device[u'serialNumber'])

lastConnected.append(device[u'lastConnected'])

#location.append(device['location'])
try:
temp=device[u'location']
except:
location.append("No location")
else:
location.append(device[u'location'])

#print(temp)
#if (device[u'location']) in data:
# print(device[u'location'])

df = pd.DataFrame([id,name,tags,osName,systemModel,serialNumber,lastConnected,location]).T

#df = df.apply(lamba location: location.str.replace(',',' ',))
#df = df.replace(',',' ', regex=True)
#df['location'] = df['location'].replace('[',' ', regex=True)
#df['location'] = df['location'].replace(']',' ', regex=True)

df.columns = ['id','name','tags','osName','systemModel','serialNumber','lastConnected','location']

#print(data)

#df.to_csv('output.csv',
#sep=',', encoding =
#'utf-8', index=False)

connStr = pyodbc.connect(
"DRIVER={SQL Server};"
"SERVER=#######;"
"Database=#####;"
#"Trusted_Connection=yes;"
"UID=ciphrconnector;"
"PWD=#######"
)
cursor = connStr.cursor()

for index,row in df.iterrows():
cursor.execute("INSERT INTO dbo.Meraki_Staging_API([MID],[name],[tags],[osName],[systemModel],[serialNumber],[lastConnected],[location]) values (?,?,?,?,?,?,?,?)", row['id'],row['name'],row['tags'],row['osName'],row['systemModel'],row['serialNumber'],row['lastConnected'],row['location'])
connStr.commit()
cursor.close()
connStr.close()

That is an example of my code. the column with the issue is tags, at the moment the data in the tags columns shows like this

"tags": [

"Corporate_Apps",

"Jabber",

"London",

"Okta",

"VBDevice",

"exchage2",

"iPhone",

"mobile"

],

"tags": [

"Laptop",

"OwnCloud",

"Shanghai"

],

So am guessing is not going into the database because of the [] and "" around the list of words in it. At the moment the if statement works but only pulls in the No tags available into the DB tables but not other tags.

I hope this helps

Thanks

BrechtSchamp · ‎Nov 19 2019

Okay, without debugging the code, does this help you:

import json
import requests

url = "h t t p s ://api.meraki.com/api/v0/networks/XXXXXXXXXXXXXXX/sm/devices"

headers = {
    'Accept': "*/*",
    'X-Cisco-Meraki-API-Key': "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx",
    'Cache-Control': "no-cache",
    'Accept-Encoding': "gzip, deflate",
    'Connection': "keep-alive",
    'cache-control': "no-cache"
    }

response = requests.request("GET", url, headers=headers)

print(response.text)

result = json.loads(response.text)
for device in result["devices"]:
    print("- DEVICE NAME:", device["name"])

    #device["tags"] is an array containing the tags of a device

    #if you just want a comma separated string of tags
    print(" * comma-separated string of tags:")
    print(','.join(device["tags"]))

    #if you want to go over all tags of a device
    print(" * iterating over the tags")
    for tag in device["tags"]:
        print(tag)
    print("---------------------")

Tbells · ‎Nov 20 2019

Thank you and will try this.

Tbells · ‎Nov 21 2019

import JSON

import pandas as PD

import sqlalchemy

import pyodbc

#file = open('rhapitest.json')

import requests

url = "https://n142.meraki.com/########”

querystring = {"fields":["phoneNumber","location","lastConnected"]}

headers = {

'x-cisco-meraki-api-key': "#####",

'content-type': "application/json",

'accept': "application/json",

'cache-control': "no-cache",

'postman-token': "#######"

}

response = requests.request("GET", url, headers=headers, params=querystring)

data = json.loads(response.text)

#print(data)

id,name,strTags,osName,systemModel,serialNumber,lastConnected,location = [],[],[],[],[],[],[],[]

#x = 0

for device in data['devices']:

# x = x+1

# print (x)

id.append(device[u'id'])

name.append(device[u'name'])

# tags.append(device[u'tags'])

#print (device[u'name'])

#print (device[u'tags'])

temp1=(device[u'tags'])

if temp1:

temp2 = ','.join(temp1)

strTags.append(temp2)

#strTags.append=(device[u"tags"])

if not temp1:

strTags.append("")

# (device[u'tags'])

# tags = (device[u'tags'])

#print(tags[x])

# print (2)

#print (temp1)

# tags.append(device[u'tags'])

# print (tags)

# try:

# temp1=device[u'tags']

#except:

# tags.append("No tag available")

#else:

# tags.append(device[u'tags'])

osName.append(device[u'osName'])

systemModel.append(device[u'systemModel'])

serialNumber.append(device[u'serialNumber'])

lastConnected.append(device[u'lastConnected'])

#location.append(device['location'])

try:

temp=device[u'location']

except:

location.append("No location")

else:

location.append(device[u'location'])

#print(temp)

#if (device[u'location']) in data:

# print(device[u'location'])

df = pd.DataFrame([id,name,strTags,osName,systemModel,serialNumber,lastConnected,location]).T

#df = df.apply(lamba location: location.str.replace(',',' ',))

#df = df.replace(',',' ', regex=True)

#df['location'] = df['location'].replace('[',' ', regex=True)

#df['location'] = df['location'].replace(']',' ', regex=True)

df.columns = ['id','name','tags','osName','systemModel','serialNumber','lastConnected','location']

#print(data)

#df.to_csv('output.csv',

#sep=',', encoding =

#'utf-8', index=False)

connStr = pyodbc.connect(

"DRIVER={SQL Server};"

"SERVER=####;"

"Database=#####;"

#"Trusted_Connection=yes;"

"UID=#####;"

"PWD={#######}"

)

cursor = connStr.cursor()

for index,row in df.iterrows():

cursor.execute("INSERT INTO dbo.Meraki_Staging_API([MID],[name],[tags],[osName],[systemModel],[serialNumber],[lastConnected],[location]) values (?,?,?,?,?,?,?,?)", row['id'],row['name'],str(strTags),row['osName'],row['systemModel'],row['serialNumber'],row['lastConnected'],row['location'])

connStr.commit()

cursor.close()

connStr.close()

Everything works apart from the fact that the tags column data for each column shows as

"tags": [

"NUC",

"Sydney"

],

"tags": [

"FileZilla",

"London",

"NUC"

],

However, I will like it to show

MID name tags ssid wifiMac osName systemModel uuid serialNumber hasChromeMdm

642 B050949 ['NUC,Sydney'] NULL NULL Windows 8.1 Enterprise

644 NYCDC01 ['FileZilla,London,NUC'] NULL NULL Windows Server 2012 R2 (64-

I want the program to work in a way whereby all the tags for each MID are not in one column but tags

each MID should be shown at each column instead. Your help will be really helpful as I am new to writing python and API. The issues am having now is that all the tags are showing on each column instead of showing the tags linked to each MID in the SQL table

I hope this more clear.

Thank you so much for the help

Meraki

Community

Get words in a [ ] in separate words

Get words in a [ ] in separate words