cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Automatically Update World Map Points Based On Custom Property Value

< BACK TO TO THE MAGIC OF SQL SCRIPTS SERIES LIST

Challenge:

  • At present moment (NPM v11) it is not possible to automatically handle nodes placement on the map
  • In the future (NPM v12) it will be possible to do so by utilising "Location" field on the SNMP devices. This will still leave few limitations:
    • first of all not all nodes are SNMP
    • second, SNMP field is not controlled by monitoring team - hence - very difficult to ensure consistency across the board

Solution

(1)

Create node custom property field "n_site_id" and make it drop down, with more or less meaningful names (better IDs) for your sites. You may already have one - in this case simply fine-tune SQL script below for it to work

04.PNG

(2)

Create new table in auxiliary database called "Sites"

05.PNG

Note:

LocationID field will not be used in this solution, but we will need it in other solutions. So, if you have intentions of following my future SQL magic series - please create this column as well

(3)

Populate this table with information about your sites, including coordinates, which you can extract from Google Map URL

06.PNG

03.PNG

(4)

Now, here is were Magic begins... below is a SQL script which you can use to populate your World Map Points.

MERGE SolarWinds.dbo.WorldMapPoints AS target

USING

  ( SELECT

       n.NodeID

      ,n.EntityType

      ,s.Latitude

      ,s.Longitude

      ,s.[Description]

    FROM SolarWinds.dbo.Nodes n

      LEFT JOIN SolarWindsAux.dbo.Sites s ON s.SiteID=n.n_site_id

   LEFT JOIN SolarWinds.dbo.WorldMapPoints wm ON n.NodeID=wm.InstanceID

  ) AS source (NodeID, EntityType, Latitude, Longitude, [Description]) ON (target.InstanceID = source.NodeID)

-- Delete from MAP if node no longer exist in Nodes table

WHEN NOT MATCHED BY source

THEN DELETE

-- Delete from MAP if Latitude/Longitude values were not specified

WHEN MATCHED

  AND (source.Latitude IS NULL or source.Longitude IS NULL)

THEN DELETE

-- Update if Latitude/Longitude were changed

WHEN MATCHED

  AND source.Latitude <> target.Latitude

  OR (source.Latitude IS NOT NULL AND target.Latitude IS NULL)

  OR source.Longitude <> target.Longitude

  OR (source.Longitude IS NOT NULL AND target.Longitude IS NULL)

  OR source.[Description] <> target.StreetAddress

  OR (source.[Description] IS NOT NULL AND target.StreetAddress IS NULL)

THEN

UPDATE SET Latitude = source.Latitude, Longitude = source.Longitude, StreetAddress = source.[Description]

-- Synch new node

WHEN NOT MATCHED BY target

  AND source.EntityType IN ('Orion.Nodes','Orion.VIM.Hosts','Orion.VIM.VCenters')

    --Ideally, everything from NODES should be included, but I don't know if all objects from Nodes can be displayed on the map.

    --So far, I know that this values will definitely work (they will be replaced with 'Orion.Nodes' values in World Map table)

  AND source.Latitude IS NOT NULL

  AND source.Longitude IS NOT NULL

THEN

INSERT (Instance, InstanceID, Latitude, Longitude, StreetAddress)

VALUES ('Orion.Nodes', source.NodeID, source.Latitude, source.Longitude, source.[Description])

;

Remember to change database name

(5)

The last thing which is left - schedule the above script to fire automatically, on schedule. Script is very resource-friendly - you can run it as often as you wish. How often - this will depend on your environment. Main questions to answer would be - how often location changes are being made by your team across all nodes and how quickly you want them to be populated on the map. In my case I decided to stick with "every 1 hour"

Please refer to the below step-by-step guide from MSDN on how to create SQL scheduled job:

Schedule a Job

(6)

... in 1 hour ...

001.PNG

Afterword

Obviously the above solution will fit into scenario, whereby you are managing your nodes locations with custom properties. However, the logic still the same - keep all your locations and coordinates separate and centrally accessible. Then - fiddle with SQL script a little bit to suite your needs and requirements. Feel free to ask any questions about adopting this script in your environment

To Your Success,

Alex Soul

www.pixace.com

Comments

One word... Impressive!

Hi Alex Soul,

These are really great instructions.  thank you!  BTW, I am not sure if you are running into the same issue I am but I am trying to write a script that uses a google maps or mapquest API to look up these coordinates in bulk because  I have a lot of sites that I want to display on the map and would like an automated way to do this.  Have you looked into this at all?  I am just starting and it looks possible but I haven't found anyone that has done a good write up on how to do it. 

Hi Johnny, thank you for your reply.

How many different sites do you have? The truth is - you only need to do this once per site. Yes, it is pain at the beginning to gather information about all sites, but think about it as about "mini audit". You not only looking up coordinates, but you also at the same time ensure that address is correct, IDs are correct, description is appropriate, etc. All this information is then stored centrally and being displayed on every node with the help of custom SQL query as described.

So, having said this - I would not recommend investing a lot of time into developing a script. Just roll your sleeves and get your sites information in order, once and for all.  Later, when you will be adding a new sites, you still need to record address, description, etc. So, why not take another 2 extra minutes to lookup coordinates as well?


... if you will invest 4 hours in developing a workable bullet proof solution (you must be freaking good with Google's APIs then) - it will pay off only after you add at least 120 sites

Regards,

Alex

Thanks Alex Soul.  I also want to plot some of my mobile equipment  on the worldwide map style so doing this in an automated way is really a requirement.  I will let you know what I come up with.

thanks

I should point out that SAM 6.2 and NPM 11.5 will automatically update the World Map based on the devices SNMP Location Field. While perhaps not as flexible as Custom Properties, it is helpful in situations where the SNMP Location is properly populated since it's essentially automagical from there.

Thanks, I am trying it out on a dev system.  So far I have not had good luck with the worldwide map plotting based on this field.

... based on which field?

... oh, pardon me, Location Field I believe Do you have SAM 6.2?

Thank you so much Alex Soul . this is very helpful.

sorry., very busy.  Yes location field.

This is a way better solution than the SNMP Location field.  I have job sites that move all over so I just edit the custom properties field.

In my Cisco devices it picks up on the field snmp-server location.  You have to test the address at http://www.openstreetmap.org, this is where it pulls from, I had to adjust some of my strings to what that website recognized, for the most part it populated most with no intervention.

Expand on this to add a Location name to the World Map with out the Unknown I have added a column called City to the dbo.City Table

Then update the SQL with that to come out with the following:

MERGE SolarWindsOrion.dbo.WorldMapPoints AS target 

USING 

  ( SELECT 

  n.NodeID 

   ,n.EntityType 

   ,s.Latitude 

   ,s.Longitude

  ,s.City 

   FROM SolarWindsOrion.dbo.Nodes n 

   LEFT  LEFT JOIN SolarWindsOrionAux.dbo.Sites s ON s.SiteID=n.n_site_id 

   LEFT JOIN SolarWindsOrion.dbo.WorldMapPoints wm ON n.NodeID=wm.InstanceID 

  ) AS source (NodeID, EntityType, Latitude, Longitude, City) ON (target.InstanceID = source.NodeID) 

WHEN NOT MATCHED BY source 

THEN DELETE 

WHEN MATCHED 

  AND (source.Latitude IS NULL or source.Longitude IS NULL or source.City IS NULL) 

THEN DELETE 

WHEN MATCHED 

  AND (source.Latitude <> target.Latitude or source.Longitude <> target.Longitude or source.City <> target.StreetAddress) 

THEN 

UPDATE SET Latitude = source.Latitude, Longitude = source.Longitude  , StreetAddress = source.City

WHEN NOT MATCHED BY target 

  AND source.EntityType IN ('Orion.Nodes','Orion.VIM.Hosts','Orion.VIM.VCenters') 

  AND source.Latitude IS NOT NULL 

  AND source.Longitude IS NOT NULL

  AND source.City IS NOT NULL 

THEN 

INSERT (Instance, InstanceID, Latitude, Longitude, StreetAddress) 

VALUES ('Orion.Nodes', source.NodeID, source.Latitude, source.Longitude, source.City) 

The main lines that changed are any with City.   To fix what you already might have in the World Database you need to remove all Nulls from the StreetAddress filed to get this to work.

Now I have City names for the sites on my World Map

pastedImage_0.png

Any possible way to do this without an additional dbase?

This is incredible improvement. I have updated script above to add this. I have used [Description] field to populate StreetAddress. Could have used [Address] field, but I don't want too much clutter in those popups, so, description looks like perfect candidate. Besides, sometimes I would want just a short name, "Texas DC1" for example or "London DR Site" instead of full address - bit more meaningful

Thank you for sharing

P.S. I have also made this SQL to pick up on existing NULLs. So, no need to handle them separately - just use SQL above - it will take care of existing NULLs itself and will populate all those that are missing

I would like that as well,  but i suppose the purpose of the separate DB was to segregate the responsibilities to a different person with no accountability or access to the primary database.

Alex if you can please let me know if the solution can work with by just adding a new table?

vikkyg86‌, You can substuite the dbase with custom tables in the SolarWinds dbase. See Custom Tables‌. Just because of what you name the table.

I thought the aux database was to protect you from breaking the world map if there was a version update to the core database.

This is a really cool solution, just set this up.

thanks mate for pointing me in the right direction, appreciated.

Hi Guys, in my latest deployment I started to create tables right within SlarWinds database. The main idea for using separate AUX database was to segregate custom setup from what comes out-of-the-box. This way I was pretty confident that my custom tables will not be wiped-out upon the next upgrade. However, in light of the response above I think I will changed my approach from now on. So, from the perspective of what this script does - it doesn't matter where your table sit

I would hold off for just a bit. I ran in to a BIG issue and I am not sure what the cause it. My custom table is gone. At this point I do not know if it has anything to do with SolarWinds, or if there was something done to the test server by our database group. I am going to look into it and will keep you all updated.

I would hold up, and see my post above.

If someone was just to do this, and none of the other "mods" you have done, wouldn't it be possible to just create a lat/lon custom property and run the script off of that? I would think so, but I'm not sure how to edit your script. I don't really know SQL, and adding and updating a table is about all I can do at the moment.

It is possible indeed and you are right - there is a bit SQLing involved, but principle is exactly same as in above script - you take a bunch of records > you compare them to another table > you update/insert/delete as required. It is basically sync between two tables, which is being accomplished with MERGE SQL function. My dear friend danbran‌ has shared with me awesome resource recently - here it is: T-SQL Step by Step Course - Mr.Bool Courses Take a look at lesson #37 (funny enough - the last one must be very advanced though... lol) - this is exactly what you see here

Word of caution: I was also considering an option to keep it all more or less with minimum "mods", but it would look quite clunky and I am actually doing much more with sites records in custom table than just sticking them on the map. Take a look here - there are at least 3 different solutions that are using this Site table for reference of different things and I have bunch more pending to be published soon. So, it definitely comes handy.

If you are still not convinced - I would recommend to construct your lat/lon custom property drop-down values in such a way so that you have site name/id there as well - for example "Big Ben | 51.500 | -0.124". Use delimiters of your choice. Then, with SQL you will need to separate this into 3 different values - Site, Lat, Lon - and then feed this into Worldmap table. Take a closer look at this custom property now - it is really taking a shape of a table. So, why not use one instead?

P.S. If you really want it quick without learning a bit of SQL yourself - PM me, I will help you for f(r)ee

This is very odd ... can you tell me how did you name it? I have few custom tables (Orion Platform 2014.2.1, SAM 6.1.1, QoE 1.0, NCM 7.3.2, NPM 11.0.1) sitting there with no problems. What is your SW version? Are there any backup/restore jobs in place? SQL jobs?

Orion Platform 2015.1.2, NPM 11.5.2, DPA 10.0.0, QoE 2.0, NTA 4.1.1, IVIM 2.1.0, SAM 6.2.2


As for jobs, I have an help desk ticket out to our database team to see if that is the case.

ok, I have seen your post: Custom Tables So, we will track this issue there...

You will never guess what the answer is. I found out that you must make sure you are connected to the correct database. I really do feel dumb right now. TGIF.

ignore this and carry on.

Lol... Here we go... You are learning my friend

alexslv​,

How would I go about having the two tables/databases match on the LocationID or the Address?

would I just have to change:

LEFT  LEFT JOIN SolarWindsOrionAux.dbo.Sites s ON s.SiteID=n.n_site_id

to:

LEFT  LEFT JOIN SolarWindsOrionAux.dbo.Sites s ON s.SiteID=n.LocationID

I am looking at over 500 sites (some with just one device). When adding, a short name may not be the best for our users.

The idea of SiteID field is that it must be unique across all your sites,

which will then allow to uniquely map this to database entry, where you

would have corresponding unique coordinates. Having over 500 sites would

mean that your dropdown list would have 500 options AND your database would

have same amount of site entries. This is a massive list indeed.

If you want to display 500 different sites on your map, then you have to

map them uniquely, same as described here. If, however, you are ok with

some sites to be merged into single dot on the map - then instead of SiteID

custom property I would use LocationID custom property and then I would

match this with LocationID from locations table. Keep in mind though that

you would need to have your coordinates in Locations table in this case.

Sites table would become obsolete

Instead of relying upon a database to populate the node with the correct Long/Lat.  Would it not be easier to just populate the World Map with a group that contains the nodes.

I have setup groups for all of our locations, the groups are populated via dynamic query based on the custom property of SiteCode for the node.  IE all nodes with a sitecode COLO would be placed in the Cologne, Germany group (EU-COLO).   The EU-COLO group is created with custom properties of Long/ and Lat.  Wonder if it is possible to populate the World Map with only Groups and place them using these custom properties.

I am somewhat new to Solarwinds and have no experience with SQL queries. 

I'm also interested to see what solarwinds can do with the new range of cisco routers that have built in gps chips. the gps has a mib and you can poll them now via snmp. I would love to be able to use that with our deployable field equipment and have it auto populate straight onto a map with its gps position instead of having to manually plot or update the location on the router.

example router

Cisco 829 Industrial Integrated Services Routers Data Sheet - Cisco

alexslv

I am wanting to remove these changes in our test environment to test other ways, how would you go about undoing this? The 1st thing I tried was to delete n_site_id and this is what I get:

Some custom properties cannot be deleted:

• n_site_id for Orion.Nodes is used to define a group expression.

I guess you have used it as a dynamic rule in one (or more) of your groups. Check all Group settings

Looks like this was the case. Why I used this to set up a group has escaped me.

First off this is a great post!!

For NPM12-

I had a thought of a way to do this without the extra DB, but it'll require the field used by the WorldWideMap to be changed from Location -

1) Create a node custom property of "Address"

2) Create an Alert that searches for a NULL or empty Address custom property and also where the Location = <YOUR SITE CODE / AD Site>.  Create an Alert action that sets the Address custom property to the address.

3) Update WorldWideMap to use the Address custom property rather than the Location (which has limitations)

Anyone any thoughts.

would you please elaborate where you will hold coordinates

Couldn't you still use the same table dbo.WorldMapPoints?  (I've never manually added any coordinates so I assumed it was done from a lookup against the "Location" field against the OpenStreetMap?)

Some people didn't want the additional table, and wanted the lat, long, and address as a cp.  (it requires more db space - but it's a small site)

pastedImage_3.png

But when I did, the map does not update properly.  I get "0 Objects at this location".

pastedImage_0.png

But the table looks correct.

pastedImage_1.png

Any ideas?

which Orion version you are using? Send me copy-paste of the footer on any SolarWinds page with products and versions

By the way, if you decided to go Custom Property route - you do not need all that fancy scripting. Please refer here: Place objects into the map using custom properties

pastedImage_0.png

Brand new install on a freshly spun up VM test sever.

I did try that route.  I populated a few lat long in the CP, and waited an hour.  But nothing happened.  The worldmap table remained empty.

Ah, ok. Then I strongly suggest you simply log this with SW support. Based on the article I have an impression that this is some kind of supported way of populating nodes on map with custom properties. Let us know here of an outcome please

Is there any way to do this automatically?

Hey alexslv​. With that link to place objects onto the map using custom properties; it's still a manual process - you have to populate the custom properties, export to a file, and then import directly into the specific World Map properties during the custom property import process. There is no custom property field that SolarWinds will read automatically to populate (I'm not including SNMP Location field and Auto-geolocation enabled).

I've used the script above successfully with a separate table but as per cscoengineer​, if I try and modify the script to use custom properties it doesn't work properly - locations appear on the map but say 0 objects at this location. Once you edit the location in the Manage World Map view, and save the change it works fine - but you have to do this for each location. Not possible if you have hundreds of locations.

cscoengineer​ - did you end up getting it working?

I'm trying to get it using custom properties as this will be for a system where the users will only be able to update custom properties and not a separate database table (sites change 10+ times a month and they won't have the access or know-how).

Thanks, never new that as I have never tried really. Ok, in this case I am even more excited about my approach I have been taking so far (described above in the article). Nowadays I do not create Auxiliary database anymore though. I create tables directly in Orion Database and prefix them with "AUX_"  - works just fine

here is a few examples:

pastedImage_0.png

Version history
Revision #:
1 of 1
Last update:
‎12-17-2014 05:54 AM
Updated by: