Closed

Closed due to inactivity. Received 60 votes with last vote on 01 Oct 2019.

Worldmap - Automatic update of GPS Coodinates

I would love to have a way to auto-update/fill GPS coordinates for some devices. We can poll GPS coordinates via SNMP.

On every RF site, we have GPS receivers (TRAK or Spectracom brand) for our telecom devices. We have also mobile devices (rigs in cars) that we would love to have updated.

The idea behind that is that we could update GPS coordinates for either all devices inside a group or for a group on the worldmap.

That would be a cool feature to have!

  • So, my follow-up question will be: can I assume that all objects in Nodes table can be shown on the WorldMap? If yes - I will simply populate Instance column with "Orion.Nodes" string, regardless of the true EntityType of an object

  • Thank you for sharing.

    Just playing with this now... this script will not include ESX hosts with "Orion.VIM.Hosts" as an EntityType. Interesting to note however, when I add ESX host manually, in the WorldMapPoint table it appears as "Orion.Nodes" in Instance column. So, my conclusion will be: Node's EntityType is not necessarily equals to WorldMapPoints Instance.

  • Again, my SQL is feeble at best, but this is my understanding of how it works commented below. I have run this script against the database probably 7 times now to update the Map Points and it has worked flawlessly. I wouldn't guarantee that there are no ill effects using this method, but so far my system has not had any noticable issues. I also exported the Nodes and WorldMapPoints tables to excel and did before and after comparisons, only the WorldMapPoints table is affected, Nodes table contents remained unchanged.

    USE NetPerfMon
    --Tells SQL engine which DB to execute the following commands against

    DELETE FROM WorldMapPoints;
    --Clears out all existing rows in the WorldMapPoints table

    INSERT INTO WorldMapPoints (Instance, InstanceID, Latitude, Longitude)
    --Selects the destination table and columns for insert from the source table
    SELECT EntityType, NodeID, Latitude, Longitude FROM Nodes
    --Selects the source table and columns for the destination insert
    WHERE EntityType = 'Orion.Nodes' AND Nodes.Latitude IS NOT NULL AND Nodes.Longitude IS NOT NULL
    --Filters the selected rows for insert to where only Orion.Nodes EntityTypes are selected and where Latitude and Longitude columns are not empty

  • FormerMember
    FormerMember in reply to rmatovich

    Thank you for the SQL script.  We also have our Latitude and Longitude coordinates stored as Custom Node properties.  My SQL knowledge is not very good either so I have a question about the script.  It doesn't seem the statements should be executed in the order given or the Insert should be a compound statement using the Select as a sub-query.  Am I off base here?

    Thanks.

  • We already had our Latitude and Longitude coordinates stored as Custom Node attributes. Here's a quick SQL script to rebuild the WorldMapPoints table with all Nodes that have Latitude and Logitude not Null. I just run this each time I need to add nodes to the map. I'm sure a SQL UPDATE command would be better, but my SQL is bad and I know this works just fine. In my case, it's around 1800 Nodes on the map.

    DELETE FROM WorldMapPoints;

    INSERT INTO WorldMapPoints (Instance, InstanceID, Latitude, Longitude)
    SELECT EntityType, NodeID, Latitude, Longitude FROM Nodes
    WHERE EntityType = 'Orion.Nodes' AND Nodes.Latitude IS NOT NULL AND Nodes.Longitude IS NOT NULL

  • Unsupported hack, but this could likely be accomplished via the following:

    1. Identify Node that you want to update GPS locations for:


    9-4-2014 2-40-37 PM.png

    2. Place Node on a World Map to get record information:

    9-4-2014 2-41-51 PM.png


    3. Set up UnDP to gather information

    1. This will be specific per device and OID
    2. Map SQL UPDATE statement to update GPS coordinates in the WorldMapPoints table:

    UPDATE [dbo].[WorldMapPoints]

    SET Latitude =

    (SELECT TOP 1 cps.RawStatus FROM [dbo].[CustomPollerStatus] cps

    JOIN [dbo].[CustomPollerAssignment] cpa ON cps.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID

    WHERE cpa.NodeID = @NodeId AND cpa.CustomPollerAssignmentID = @myCustomPollerAssignmentID AND cps.RowID = @latitudeRowID)

    WHERE InstanceID = @NodeId AND Instance = 'Orion.Nodes'

    UPDATE [dbo].[WorldMapPoints]

    SET Longitude =

    (SELECT TOP 1 cps.RawStatus FROM [dbo].[CustomPollerStatus] cps

    JOIN [dbo].[CustomPollerAssignment] cpa ON cps.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID

    WHERE cpa.NodeID = @NodeId AND cpa.CustomPollerAssignmentID = @myCustomPollerAssignmentID AND cps.RowID = @longitudeRowID)

    WHERE InstanceID = @NodeId AND Instance = 'Orion.Nodes'



  • This will be great. I cant wait until they release a feature like that