Automatically Update World Map Points Based On Custom Property Value

Version 3

    < 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