The CaNerdIan

Yesterday, I posted about leveraging the Meridian API to get a list of placemarks into a CSV file. Today, We’ll take that one step further, and go the other way – because bulk creating/updating placemarks is no fun by hand.

For instance, in this project, I created a bunch of placemarks called “Phone Room” (didn’t know what else to call them at the time). There were several of these on multiple floors. To rename them in the Meridian Editor, I would have to click on each one, rename it, and save.

So, once I got guidance on what they were to be called, I fired up Excel and opened up the CSV that I created yesterday, and made the changes in bulk, and then ran them back into Meridian the other way – I changed the name, the type, and the icon:

Sounds easy, right?

Not so much. I ran into some trouble when I opened it in excel, and all my map IDs looked like this:

This is because Excel is stupid, but trying to be smart. It sees those as Really Big Numbers, and converts them to scientific notation, because the largest it can store is a 15-digit integer. And of course, these map IDs are… 16 digits. But I can’t just convert them back as integer number formatting because it then takes the first 15 digits and adds a zero. This, of course, breaks the whole thing. Excel will also do some similar shenanigans when parsing interface names from AOS or Cisco that look like “2/1/4”, which excel assumes is a date, because excel assumes everything that looks vaguely numeric must be a number, because it is a spreadsheet after all, and spreadsheets are made for numbers, even if people abuse them all the time as a poor substitute for a database.

So, this means you either have to make the changes directly in the CSV with a text editor, or find another sheets application that doesn’t mangle the map IDs. Fortunately, for us Mac users, Apple’s spreadsheet application (“Numbers”) handles this just fine. So make the changes, export to CSV, and run it all back into the API. (you can also name it as a .txt and manually import into Excel and specify that as a text column, but that’s tedious, which is what we’re trying to avoid)

I’ve built a bit of smarts into this script, since I don’t want to break things on Meridian’s end (although Meridian does a great job of sanity checking and sanitizing the input data from the API). The first thing it does is grab a list of available maps for the location. Then it goes through all the lines in the spreadsheet, converts them to the JSON payload that Meridian wants, and checks to see if there’s existing data in the id field. If there is, it assumes that this is an update (it does not, however, check to see if the data already matches the existing placemark since Meridian does that already when you update). If there is no ID, it assumes that this is a new object to be created, and verifies that it has the minimum required information (name, map, and x/y position), and in both cases, checks to make sure the map data in the object is a map ID that exists at this location (this is how I found out that excel was mangling them)

Running the script spits out this for each row in the CSV that it considers an update:

Update object id XXXXXXXXXXXXXXX_5666694473318400
object update passed initial sanity checks and will be placed on 11th Floor.
Updating existing object with payload:
{
  "id": "XXXXXXXXXXXXXXX_5666694473318400",
  "name": "Huddle Space",
  "map": "XXXXXXXXXXXXXXX",
  "type": "conference_room",
  "type_name": "Conference Room",
  "color": "f2af1d",
  "x": "177.20516072322900",
  "y": "597.6184874989240",
  "latitude": 41.94822,
  "longitude": -87.65552,
  "area": "",
  "description": "",
  "phone": "",
  "email": "",
  "url": ""
}
Object ID XXXXXXXXXXXXXXX_5666694473318400 named Huddle Space updated on map XXXXXXXXXXXXXXXX

If it doesn’t find an id and determines that an object needs to be created, it goes down like this:

Create new object: 
object create passed initial sanity checks and will be placed on 11th Floor.
Creating new object with payload:
{
  "name": "Test Placemark",
  "map": "XXXXXXXXXXXXXXXX",
  "type": "generic",
  "type_name": "Placemark",
  "color": "f2af1d",
  "x": "400",
  "y": "600",
  "latitude": "",
  "longitude": "",
  "area": "",
  "description": "",
  "phone": "",
  "email": "",
  "url": "https://arubanetworks.,com"
}
Object not created. Errors are
{
  "url": [
    "Enter a valid URL."
  ]
}

As you can see here, I made a typo in the URL field, and the data returned from the API call lists the fields that contain an error. If the call is successful, it returns an ID, which the script checks for to verify success. The response from a successful API call looks like this :

{
  "parent_pane": "",
  "child_pane_ne": "",
  "child_pane_se": "",
  "child_pane_sw": "",
  "child_pane_nw": "",
  "left": -1,
  "top": -1,
  "width": -1,
  "height": -1,
  "next_pane": null,
  "next_id": "5484974943895552",
  "modified": "2021-08-11T15:12:52",
  "created": "2021-08-11T15:12:52",
  "id": "XXXXXXXXXXXXXXXX_5484974943895552",
  "map": "XXXXXXXXXXXXXXXX",
  "x": 400.0,
  "y": 600.0,
  "latitude": 41.94822,
  "longitude": -87.65552,
  "related_map": "",
  "name": "Test Placemark",
  "area": null,
  "hint": null,
  "uid": null,
  "links": [],
  "type": "generic",
  "type_category": "Markers",
  "type_name": "Placemark",
  "color": "88689e",
  "description": "",
  "keywords": null,
  "phone": "",
  "email": "",
  "url": "https://arubanetworks.com",
  "custom_1": null,
  "custom_2": null,
  "custom_3": null,
  "custom_4": null,
  "image_url": null,
  "image_layout": "widescreen",
  "is_facility": false,
  "hide_on_map": false,
  "landmark": false,
  "feed": "",
  "deep_link": "com.arubanetworks.aruba-meridian://ZZZZZZZZZZZZZZZZ/placemarks/XXXXXXXXXXXXXXXX_5484974943895552",
  "is_disabled": false,
  "category_ids": [],
  "categories": []
}

Of course, the script doesn’t have to spit out all that output, but it’s handy to follow what’s going on. Comment out the print lines if you want it to shut up.

So, without further ado, here’s the script. This has not been debugged extensively, so use at your own risk. If you break your environment, you probably should have tested it in the lab first.

#!/usr/bin/python3

# Aruba Meridian Placemark Import from CSV
# (c) 2021 Ian Beyer
# This code is not endorsed or supported by HPE

import json
import requests
import csv
import sys

auth_token = '<please insert a token to continue>'
location_id = 'XXXXXXXXXXXXXXXX'

baseurl = 'https://edit.meridianapps.com/api/locations/'+location_id


header_base = {'Authorization': 'Token '+auth_token}

def api_call(method,endpoint,headers,payload):
	response = requests.request(method, baseurl+endpoint, headers=headers, data=payload)
	resp_json = json.loads(response.text)
	return(resp_json)

#File name argument #1
try:
	fileName = str(sys.argv[1])
except:
	print("Exception: Enter file to use")
	exit()


# Get available maps for this location for sanity check
maps={}

# print("Available Maps: ")
for floor in api_call('GET','/maps',header_base,{})['results']:
 	maps[floor['id']] = floor['name']
# 	print (floor['name']+ ": "+ floor['id'])



import_data_file = open(fileName, 'rt')

csv_reader = csv.reader(import_data_file)
count = 0

objects = []

csv_fields = []

for line in csv_reader:
	placemark = {}

	# Check to see if this is the header row and capture field names
	if count < 1 :
		csv_fields = line
	else:
		# If this is a data row, capture the fields and put them into a dict object
		fcount = 0
		for fields in line:
			objkey = csv_fields[fcount]
			placemark[objkey] = line[fcount]
			fcount += 1

		# Add the placemark object into the object list
		objects.append(placemark)		
	count +=1

#print(json.dumps(objects, indent=2))

import_data_file.close()

#Check imported objects for create or update. If it has an ID, then update. 
for pm in objects:
	task = 'ignore'
	if pm['id'] == "" :
		task = 'create'
		print("Create new object: ")
		# Delete id from payload
		del pm['id']
	else:
		task = 'update'
		print("Update object id "+ pm['id'])


	# Remove floor from payload as it is not valid
	del pm['floor']

	# Check to see if the basics are there before making the API calls
	reject = []
	if pm['x'] == "":
		reject.append("Missing X coordinate")
	if pm['y'] == "":
		reject.append("Missing Y coordinate")
	if pm['map'] == "":
		reject.append("Missing map id")
	if pm['name'] == "":
		reject.append("Missing object name")

	if len(reject)>0:
		#print("object "+ task + " rejected due to missing required data:")
		for reason in reject:
			print(reason)
		task = 'ignore'
	else:
		if maps.get(pm['map']) == None:
			print ("Map ID "+pm['map']+" Not found in available maps. Object will not be created. ")
			task = 'ignore'
		else:
			print("object "+ task + " passed initial sanity checks and will be placed on "+ maps[pm['map']] +".")


	#print ("Object Payload:")	
	#print (json.dumps(pm, indent=2))

	method = 'GET'
	
	if task == 'create':
		#print ("Creating new object with payload:")	
		#print (json.dumps(pm, indent=2))
		method = 'POST'
		ep = '/placemarks'
		result = api_call(method,ep,header_base,pm)

		if result.get('id') != None:
			print ("Object ID "+result['id']+" named "+result['name']+ " created on map "+ result['map'])
		else:
			print ("Object not created. Errors are")
			print (json.dumps(result, indent=2))
	if task == 'update':
		#print ("Updating existing object with payload:")	
		#print (json.dumps(pm, indent=2))
		method = 'PATCH'
		ep = '/placemarks/'+pm['id']
		result = api_call(method,ep,header_base,pm)

		if result.get('id') != None:
			print ("Object ID "+result['id']+" named "+result['name']+ " updated on map "+ result['map'])
		else:
			print ("Object not updated. Errors are")
			print (json.dumps(result, indent=2))


baseurl = 'https://edit.meridianapps.com/api/locations/'+location_id


header_base = {'Authorization': 'Token '+auth_token}

def api_call(method,endpoint,headers,payload):
	response = requests.request(method, baseurl+endpoint, headers=headers, data=payload)
	resp_json = json.loads(response.text)
	return(resp_json)


# Get available maps for this location for sanity check
maps={}

# print("Available Maps: ")
for floor in api_call('GET','/maps',header_base,{})['results']:
 	maps[floor['id']] = floor['name']
# 	print (floor['name']+ ": "+ floor['id'])


# I've hard coded the file name here because I'm lazy. 

import_data_file = open('placemarks_update.csv', 'rt')

csv_reader = csv.reader(import_data_file)
count = 0

objects = []

csv_fields = []

for line in csv_reader:
	placemark = {}

	# Check to see if this is the header row and capture field names
	if count < 1 :
		csv_fields = line
	else:
		# If this is a data row, capture the fields and put them into a dict object
		fcount = 0
		for fields in line:
			objkey = csv_fields[fcount]
			placemark[objkey] = line[fcount]
			fcount += 1

		# Add the placemark object into the object list
		objects.append(placemark)		
	count +=1

#print(json.dumps(objects, indent=2))

import_data_file.close()

#Check imported objects for create or update. If it has an ID, then update. 
for pm in objects:
	task = 'ignore'
	if pm['id'] == "" :
		task = 'create'
		print("Create new object: ")
		# Delete id from payload
		del pm['id']
	else:
		task = 'update'
		print("Update object id "+ pm['id'])


	# Remove floor from payload as it is not valid
	del pm['floor']

	# Check to see if the basics are there before making the API calls
	reject = []
	if pm['x'] == "":
		reject.append("Missing X coordinate")
	if pm['y'] == "":
		reject.append("Missing Y coordinate")
	if pm['map'] == "":
		reject.append("Missing map id")
	if pm['name'] == "":
		reject.append("Missing object name")

	if len(reject)>0:
		#print("object "+ task + " rejected due to missing required data:")
		for reason in reject:
			print(reason)
		task = 'ignore'
	else:
		if maps.get(pm['map']) == None:
			print ("Map ID "+pm['map']+" Not found in available maps. Object will not be created. ")
			task = 'ignore'
		else:
			print("object "+ task + " passed initial sanity checks and will be placed on "+ maps[pm['map']] +".")


	#print ("Object Payload:")	
	#print (json.dumps(pm, indent=2))

	method = 'GET'
	
	if task == 'create':
		#print ("Creating new object with payload:")	
		#print (json.dumps(pm, indent=2))
		method = 'POST'
		ep = '/placemarks'
		result = api_call(method,ep,header_base,pm)

		if result.get('id') != None:
			print ("Object ID "+result['id']+" named "+result['name']+ " created on map "+ result['map'])
		else:
			print ("Object not created. Errors are")
			print (json.dumps(result, indent=2))
	if task == 'update':
		#print ("Updating existing object with payload:")	
		#print (json.dumps(pm, indent=2))
		method = 'PATCH'
		ep = '/placemarks/'+pm['id']
		result = api_call(method,ep,header_base,pm)

		if result.get('id') != None:
			print ("Object ID "+result['id']+" named "+result['name']+ " updated on map "+ result['map'])
		else:
			print ("Object not updated. Errors are")
			print (json.dumps(result, indent=2))

It’s also worth noting here that the CSV structure and field order isn’t especially important since it reads in the header row to get the keys for the dict – as long as you have the minimum data (name/map/x/y) you can create a table of new objects from scratch. Any valid field can be used (although categories requires some additional structure)

Questions/comments/glaring errors? Comment section is right here. Scripts on this page can also be found on github.

It seems fitting that the week in which I became the first to pass the CWIDP (Certified Wireless IoT Design Professional) certification would be one where I happened to be onsite doing a BLE location project with Aruba Meridian.

While the web based editor is great, it is mildly annoying that one of the things it can’t do is copy and paste placemarks between floors, which would be really handy when you’re deploying an office building that has almost the same layout on every floor. For this, you have to dig into the Meridian API.

By using the API, you can spit out a list of placemarks in JSON (easiest way to do this is in Postman, with a GET to {{BaseURL}}/locations/{{LocationID}}/placemarks), grab the JSON objects for the placemarks you wish to copy from the output, update the fields in the JSON for which map ID they need to go on, and any other data, and then make a POST back to the same endpoint with a payload containing the JSON list of objects you want to create. Presto, now you’ve been able to clone all the placemarks from one floor to another.

Note that point coordinates are relative to the map image – So cloning from one map to another can bite you in the rear if they’re not properly aligned. (If you want to get really clever, You could manually create a placemark on every floor whose sole purpose is to align each floor in code – much like an alignment point in Ekahau, when you generate a placemark list, you can calculate any offsets between them and apply those to coordinates before sending them back to the API. But this trick could (and probably will) be a whole post of its own.

The structure of a placemark object from a GET:

       {
            "parent_pane": "",
            "child_pane_ne": "",
            "child_pane_se": "",
            "child_pane_sw": "",
            "child_pane_nw": "",
            "left": -1,
            "top": -1,
            "width": -1,
            "height": -1,
            "next_pane": null,
            "next_id": "XXXXXXXXXXXXXXXXXX",
            "modified": "2021-08-06T22:57:29",
            "created": "2021-08-06T15:28:13",
            "id": "XXXXXXXXXX_XXXXXXXXXX",
            "map": "XXXXXXXXXXXXXXXX",
            "x": 2152.0189227788246,
            "y": 499.59448403469816,
            "latitude": 41.94822,
            "longitude": -87.65552,
            "related_map": "",
            "name": "Pitch Room",
            "area": "2132.722,480.101,2228.177,480.498,2230.003,634.737,2132.412,634.722",
            "hint": null,
            "uid": null,
            "links": [],
            "type": "conference_room",
            "type_category": "Office",
            "type_name": "Conference Room",
            "color": "596c7c",
            "description": null,
            "keywords": null,
            "phone": null,
            "email": null,
            "url": null,
            "custom_1": null,
            "custom_2": null,
            "custom_3": null,
            "custom_4": null,
            "image_url": null,
            "image_layout": "widescreen",
            "is_facility": false,
            "hide_on_map": false,
            "landmark": false,
            "feed": "",
            "deep_link": null,
            "is_disabled": false,
            "category_ids": [
                "XXXXXXXXXXXXXXXX"
            ],
            "categories": [
                {
                    "id": "XXXXXXXXXXXXXXXX",
                    "name": "Conference Room"
                }
            ]
        }

However, when creating a new placemark, you don’t need all these fields… The only objects that are absolutely required are map, type, x, and y (I haven’t tried sending an id along with a POST, so I don’t know if it will ignore it or reject it.) I’ve used Postman variables here for map and name, because then I could just change those in the environment variables and resubmit to put on multiple floors. The best part about the POST method is that the payload doesn’t just have to be a single JSON object, it can be a list of them, by simply putting multiple objects inside a list using square brackets.

{
            "map": "{{ActiveMapID}}",
            "x": 2152.0189227788246,
            "y": 499.59448403469816,
            "latitude": 41.94822,
            "longitude": -87.65552,
            "related_map": "",
            "name": "{{ActiveFloor}} Pitch Room",
            "area": "2132.722,480.101,2228.177,480.498,2230.003,634.737,2132.412,634.722",
            "hint": null,
            "uid": null,
            "links": [],
            "type": "conference_room",
            "type_category": "Office",
            "type_name": "Conference Room",
            "color": "596c7c",
            "description": null,
            "keywords": null,
            "phone": null,
            "email": null,
            "url": null,
            "custom_1": null,
            "custom_2": null,
            "custom_3": null,
            "custom_4": null,
            "image_url": null,
            "image_layout": "widescreen",
            "is_facility": false,
            "hide_on_map": false,
            "landmark": false,
            "feed": "",
            "deep_link": null,
            "is_disabled": false,
            "category_ids": [
                "5095323364098048"
            ],
            "categories": [
                {
                    "id": "5095323364098048",
                    "name": "Conference Room"
                }
            ]
        }

And if you want to update an existing placemark, simply make a PATCH call to the API with the existing placemark’s ID, and whatever fields you wish to update. Like with the POST call, you can send a payload that is a list of objects This is a great way to batch update URLS or names.

It may also come in handy to generate a list of all the placemarks at a given location. So I threw together a handy little python script that will spit it out into a CSV (and will also look up the map ID and get the floor number for easy reference).

#!/usr/bin/python3

# Aruba Meridian Placemark Export to CSV
# (c) 2021 Ian Beyer
# This code is not endorsed or supported by HPE

import json
import requests
import csv
import sys

auth_token = 'Please Insert Another Token to continue playing'
location_id = 'XXXXXXXXXXXXXXXX'

baseurl = 'https://edit.meridianapps.com/api/locations/'+location_id


header_base = {'Authorization': 'Token '+auth_token}

def api_get(endpoint,headers,payload):
	response = requests.request("GET", baseurl+endpoint, headers=headers, data=payload)
	resp_json = json.loads(response.text)
	return(resp_json)

#File name argument #1
try:
	fileName = str(sys.argv[1])
except:
	print("Exception: Enter file to use")
	exit()

maps={}

for floor in api_get('/maps',header_base,{})['results']:
	maps[floor['id']] = floor['name']

beacons=[]

# Iterate by floor for easy grouping - 
for flr in maps.keys():
	bcnlist=api_get('/beacons?map='+flr,header_base,{})
	# NOTE: If bcnlist['next'] is not null, then there are additional pages - this doesn't process those yet. 
	for bcn in bcnlist['results']:
		# Add floor name column for easier reading. 
		bcn['floor']=maps[bcn['map']]
		beacons.append(bcn)



data_file = open(fileName, 'w')
csv_writer = csv.writer(data_file)
count = 0

csv_fields = beacons[0].keys()

print(csv_fields)

csv_writer.writerow(csv_fields)

#print(placemarks)
for bcn in beacons:
	data=[]
	for col in csv_fields:
		data.append(bcn[col])
	# Writing data of CSV file
	csv_writer.writerow(data)
	count += 1
data_file.close()

print ("Exported "+str(count)+" beacons. ")

Once you have this list in a handy spreadsheet form, then you can make quick and easy edits, and then run the process the other way.

Scripts on this page can also be found on github.