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

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

    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.