This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

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

  • 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 emoticons_happy.png

    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.

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

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

  • sorry., very busy.  Yes location field.