Ya i do know SQL thanks I will look into it but before proceeding I had couple of concerns about my code to geneeate the client log events my concerns
1)The for loops that have written I am able to access all the networks,able to loop through all access points(Devices) in the network,able to loop through and get all clients in each access points but when it comea to client log events I am able to loop through and get only the last row of accesspoints list and get those particular and clients and its log events I assume I am going wrong in the last for loop code and output as shown below import requests import meraki import json serveraddress= 'meraki.com <http://n136.meraki.com/>' merakiAPIkey='3****????ce879efeafa5' organization='4 networkid='N_63951****2046' print ("Currently, the following organizations exist:\n") base_url = "https://"; + serveraddress + "/api/v0/organizations" headers = {'X-Cisco-Meraki-API-Key': merakiAPIkey} # Send the query to the Prime Infrastructure Server r = requests.get(base_url,headers=headers) # Retrieve the results in JSON format json_string = r.json() for org in json_string: print ("Organization: {}, ID: {}".format(org.get("name"), org.get("id"))) print ("Meraki Query Engine Starting...\n") #### Section 2 Getting the networkID'S#### #Construct the url for querying Prime Infrastructure base_url=("https://"+serveraddress+"/api/v0";) config_url=("/organizations/"+organization+"/networks") headers = {'X-Cisco-Meraki-API-Key':merakiAPIkey} r = requests.get(base_url+config_url,headers=headers) json_string = r.json() for network in json_string: print("Network:{}, ID: {}".format(network.get("name") ,network.get("id"))) for item in json_string: if item['id'] == "N_630" : network = item['id'] found_network = True if not found_network: print ("No Valid Network Found...") exit() print ("\n****************>>Querying Meraki for Access Points(Devices) on Network:"+network) #### Section 2 Getting the Accesspoints(Devices)#### config_url = "/networks/"+network+"/devices" r = requests.get(base_url+config_url,headers=headers) json_string = r.json() print ('{0:20} {1:20} {2:20} {3:30} {4:20}'.format("Switch Name", "MAC Address", "Serial Number", "IP Address","Device Type")) serialnumlist=[] for item in json_string: # Extract all the appropriate fields devicename = item.get("name") macAddress = item.get("mac") serialnum = item.get("serial") serialnumlist.append(item) devicetype = item.get("model") ipaddress = item.get("lanIp") # Print the resulting data to the screen print ('{0:20} {1:20} {2:20} {3:30} {4:20}'.format(devicename, macAddress, serialnum,ipaddress, devicetype)) for item in serialnumlist: print ("\n*****************>>Querying Meraki for clients on Access Points:" + item.get("name")+ " (" + item.get("serial") + ")") print ('{0:20} {1:30} {2:16} {3:18} {4:15} {5:20}'.format("Client Name", "Description", "IP Address", "MAC Address", "Sent KBytes", "Recv KBytes")) config_url = "/devices/" + item.get("serial") + "/clients?timespan=86400" r = requests.get(base_url + config_url, headers=headers) json_string = r.json() for client in json_string: hostname = client.get("dhcpHostname") description = client.get("description") ipaddress = client.get("ip") macaddress = client.get("mac") usage = client.get("usage") sentbytes = usage.get("sent") recvbytes = usage.get("recv") print ('{0:20} {1:20} {2:20} {3:30} {4:20} {5:20}'.format(hostname, description, ipaddress, macaddress,sentbytes, recvbytes)) for item in serialnumlist: print ("\nQuerying Meraki for clientlogevents on Devices: (" + item.get("mac") + ")") # print ('{0:20} {1:30} {2:16} {3:18} {4:10} {5:11}'.format("Hostname", "Description", "IP Address", "MAC Address","Sent KBytes", "Recv KBytes")) for item in json_string: config_url = "/networks/"+"/N_63***1050/"+"/clients/"+ item.get("mac") + "/events?perPage=1000" r = requests.get(base_url + config_url, headers=headers) print ('{} '.format(r.content)) **********************************> OUTPUT Network:Room, ID: N_631140260 Network:Ofce, ID: N_639540739 ***>>Querying Meraki for Access Points(Devices) on Network:N_639511050 Switch Name MAC Address Serial Number HA e0:cb:b:3e:95 Q2XD-USN6 172.16.172. AB. e0:cb:7:01:f4 Q2XD-G8Q8 172.16.172. ***********>>Querying Meraki for clients on Access Points:HR (Q2XD-USN6) Client Name MAC Address Kiosk 55:77:6a:5 Chphone. 44:91:60:a2:0 *****************>>Querying Meraki for clients on Access Points:DerbyRoom (Q2XD--GQ8) Client Name MAC Address iPhone f0:98:9d:2c: arphone c8:21:58:79:b it-min e:05:a4:c9: Querying Meraki for clientlogevents on Devices: e0:cb:b::3e:95 (HA) Not looping the devices to get api calls for this access point Querying Meraki for clientlogevents on Devices: e0:cb:7:01:f4 (AB) Getting api calls for list of devices in the access point ******* I know I am asking a lot but I hit a road block on this i am assuming One small change in my last for loop I can get this working for all devices and I have hardcoded for one network how do I use the same code for different networks as well You help is really appreciated Thank you all! On Wed, Dec 5, 2018, 2:32 AM Alan Gauld <alan.ga...@yahoo.co.uk wrote: > CCing the list, please use Reply All when responding to the tutor list. > > > On 05/12/2018 03:44, Ravi Kumar wrote: > > Yes thats right I want to extract the xml and store into database(SQL > > Server) and I will have to cteate a new table > > > > Here is the sample output I am getting similarly there bulk data which > > I want to store in database format is as shown below > > > > > > > b'[{"deviceSerial":"Q2XD-333X-G8Q8","occurredAt":1537565.640085,"type":"802.11 > > > association","details":{"radio":"1","vap":"0","clientMac":"C8:21:6679:B6:16","channel":"44","rssi":"57","aid":"31645681"}},{"deviceSerial":"Q2XD-97gX-G8Q8","occurredAt":153765.700095,"type":"WPA > > > deauthentication","details":{"radio":"1","vap":"0","clientMac":"C621:58:79:B6:16","aid":"316681"}},{"deviceSerial":"Q2XD-97gX-G8Q8","occurredAt":1563369.780085,"type":"WPA > > > deauthentication","details":{"radio":"1","vap":"0","clientMac":"C8:21:58:9:B6:16","aid":"31645681"}},{"deviceSerial":"Q297JX-G8Q8" > > > > > > Please let me whether this can be formatted into json in the code > > It looks to me like it is already in JSON format. > > It is a list of objects. > > The json module will let you extract the objects into Python data objects. > > > > and also any other tips or links to refer if available to store in > > database > > Do you know SQL? > > If so using the Python DBAPI is very easy. > > If not you will need to learn some basic SQL. > > You can try reading the database topic in my Python tutorial(see below) > which is based on SQLite rather than SQL Server but should be 90% > compatible. > > > -- > Alan G > Author of the Learn to Program web site > http://www.alan-g.me.uk/ > http://www.amazon.com/author/alan_gauld > Follow my photo-blog on Flickr at: > http://www.flickr.com/photos/alangauldphotos > > _______________________________________________ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: https://mail.python.org/mailman/listinfo/tutor
