This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

[Script] Automatic Geolocation Devices in Worldwide Map

conguir over 6 years ago

If you are one of the ones that is using the WorldWide map feature in SolarWinds, I’m sure it feels bittersweet for you. Sweet because it is a cool feature that allows you to see geographically where your devices are, but also bitter because it is not so easy to place the devices in the map, and it is still a manual process.

I know that there is the option to automatic geolocate devices based on the field ‘location’ available in SNMP, with the following KB detailing how to use this, but:

who is using that field properly?
what happens with the rest of the devices? Say devices monitored using WMI, Agent or even ICMP

There is also a way to import coordinates within the GUI of Orion, but this is a manual process, which would need to be performed regularly in order to keep objects on the map up to date.

That’s why in Prosperon Networks we have created a SQL script that will set the coordinates of the devices based on the value of a custom property. This script can be scheduled to be run every day, or any other frequency that you might consider in order to place any new device on the map.

There are just a few prerequisites in order to use this script properly:

Create a custom property named Site
Assign the correct Site to each device
Modify the SQL script adding your locations and the coordinates of those locations in Site: Add more locations just by adding more options on each case.
Create a job in SQL to run this script every day

BONUS INSIGHT

We can use IP addresses instead of the custom property site if you wish. The script would look like

…

Case

When IP_address like ‘192.168.% then 50.813104

When IP_address like ‘172.16.1.% then 41.429853

….

Kind Regards,

Raul Gonzalez

Prosperon - UK SolarWinds Partners

Installation | Consultancy | Training | Licenses

0 mwenda over 3 years ago

Hi conguir

I tried ussing the script but got so many errors. Any idea on what i could be doing wrong + need to correct?

Would you have a video tutorial of the above in any of your webinars?

Solarwinds Network Configuration Manager
Scheduled Job Notification

___________________________________________________________________________

4/10/2021 4:09:54 PM : Started Msa Lab Maps Test

Execute Command Script on Devices
1 devices selected

Devices: 1
Errors: 0
___________________________________________________________________________

Msa Lab (195.202.90.142):

MERGE dbo.WorldMapPoints AS target
MERGE dbo.WorldMapPoints AS target
    ^
% Invalid input detected at '^' marker.
USING
Translating "USING"
Translating "USING"
% Bad IP address or host name
% Unknown command or computer name, or unable to find computer address
    ( SELECT n.NodeID
    ( SELECT n.NodeID
    ^
% Invalid input detected at '^' marker.
        ,n.EntityType
% Bad IP address or host name
% Unknown command or computer name, or unable to find computer address
        ,case
% Bad IP address or host name
% Unknown command or computer name, or unable to find computer address
                                               when Site='Msa Lab' then -4.043740
                                               when Site='Msa Lab' then -4.043740
                                                  ^
% Invalid input detected at '^' marker.

                                               else null
                                               else null
                                                ^
% Invalid input detected at '^' marker.
                   end as Latitude
                   end as Latitude
                     ^
% Invalid input detected at '^' marker.
        ,case
% Bad IP address or host name
% Unknown command or computer name, or unable to find computer address
                                               when Site='Msa Lab' then 39.658871
                                               when Site='Msa Lab' then 39.658871
                                                  ^
% Invalid input detected at '^' marker.

                                               else null
                                               else null
                                                ^
% Invalid input detected at '^' marker.
                   end as Longitude
                   end as Longitude
                     ^
% Invalid input detected at '^' marker.
        ,case
% Bad IP address or host name
% Unknown command or computer name, or unable to find computer address
                                               when Site='Msa Lab' then 'Msa Lab'
                                               when Site='Msa Lab' then 'Msa Lab'
                                                  ^
% Invalid input detected at '^' marker.

                                               else null
                                               else null
                                                ^
% Invalid input detected at '^' marker.
                   end as [Description]
                   end as [Description]
                     ^
% Invalid input detected at '^' marker.
     FROM Nodes n
     FROM Nodes n
      ^
% Invalid input detected at '^' marker.
     LEFT JOIN WorldMapPoints wm ON n.NodeID=wm.InstanceID
     LEFT JOIN WorldMapPoints wm ON n.NodeID=wm.InstanceID
      ^
% Invalid input detected at '^' marker.
    ) AS source (NodeID, EntityType, Latitude, Longitude, [Description]) ON (target.InstanceID = source.NodeID)
    ) AS source (NodeID, EntityType, Latitude, Longitude, [Description]) ON (target.InstanceID = source.NodeID)
    ^
% Invalid input detected at '^' marker.

WHEN NOT MATCHED BY source
WHEN NOT MATCHED BY source
     ^
% Invalid input detected at '^' marker.
THEN DELETE
THEN DELETE
   ^
% Invalid input detected at '^' marker.

WHEN MATCHED
WHEN MATCHED
     ^
% Invalid input detected at '^' marker.
    AND (source.Latitude IS NULL or source.Longitude IS NULL)
    AND (source.Latitude IS NULL or source.Longitude IS NULL)
     ^
% Invalid input detected at '^' marker.
THEN DELETE
THEN DELETE
   ^
% Invalid input detected at '^' marker.
WHEN MATCHED
WHEN MATCHED
     ^
% Invalid input detected at '^' marker.
    AND source.Latitude <> target.Latitude
    AND source.Latitude <> target.Latitude
     ^
% Invalid input detected at '^' marker.
    OR (source.Latitude IS NOT NULL AND target.Latitude IS NULL)
    OR (source.Latitude IS NOT NULL AND target.Latitude IS NULL)
     ^
% Invalid input detected at '^' marker.
    OR source.Longitude <> target.Longitude
    OR source.Longitude <> target.Longitude
     ^
% Invalid input detected at '^' marker.
    OR (source.Longitude IS NOT NULL AND target.Longitude IS NULL)
    OR (source.Longitude IS NOT NULL AND target.Longitude IS NULL)
     ^
% Invalid input detected at '^' marker.
    OR source.[Description] <> target.StreetAddress
    OR source.[Description] <> target.StreetAddress
     ^
% Invalid input detected at '^' marker.
    OR (source.[Description] IS NOT NULL AND target.StreetAddress IS NULL)
    OR (source.[Description] IS NOT NULL AND target.StreetAddress IS NULL)
     ^
% Invalid input detected at '^' marker.
THEN
Translating "THEN"
Translating "THEN"
% Bad IP address or host name
% Unknown command or computer name, or unable to find computer address
UPDATE SET Latitude = source.Latitude, Longitude = source.Longitude, StreetAddress = source.[Description]
UPDATE SET Latitude = source.Latitude, Longitude = source.Longitude, StreetAddress = source.[Description]
    ^
% Invalid input detected at '^' marker.


WHEN NOT MATCHED BY target
WHEN NOT MATCHED BY target
     ^
% Invalid input detected at '^' marker.
    AND source.EntityType IN ('Orion.Nodes','Orion.VIM.Hosts','Orion.VIM.VCenters')
    AND source.EntityType IN ('Orion.Nodes','Orion.VIM.Hosts','Orion.VIM.VCenters')
     ^
% Invalid input detected at '^' marker.

    AND source.Latitude IS NOT NULL
    AND source.Latitude IS NOT NULL
     ^
% Invalid input detected at '^' marker.
    AND source.Longitude IS NOT NULL
    AND source.Longitude IS NOT NULL
     ^
% Invalid input detected at '^' marker.
THEN
% Bad IP address or host name
% Unknown command or computer name, or unable to find computer address
INSERT (Instance, InstanceID, Latitude, Longitude, StreetAddress)
INSERT (Instance, InstanceID, Latitude, Longitude, StreetAddress)
   ^
% Invalid input detected at '^' marker.
VALUES ('Orion.Nodes', source.NodeID, source.Latitude, source.Longitude, source.[Description])
VALUES ('Orion.Nodes', source.NodeID, source.Latitude, source.Longitude, source.[Description])
   ^
% Invalid input detected at '^' marker.
;

___________________________________________________________________________
4/10/2021 4:10:01 PM : Completed Msa Lab Maps Test
Execution time : 7s
Cancel
Vote Up 0 Vote Down

Cancel
0 mwenda over 3 years ago

My script below:

MERGE dbo.WorldMapPoints AS target
USING
( SELECT n.NodeID
,n.EntityType
,case
when Site='Msa Lab' then -4.043740

else null
end as Latitude
,case
when Site='Msa Lab' then 39.658871

else null
end as Longitude
,case
when Site='Msa Lab' then 'Msa Lab'

else null
end as [Description]
FROM Nodes n
LEFT JOIN WorldMapPoints wm ON n.NodeID=wm.InstanceID
) AS source (NodeID, EntityType, Latitude, Longitude, [Description]) ON (target.InstanceID = source.NodeID)

WHEN NOT MATCHED BY source
THEN DELETE

WHEN MATCHED
AND (source.Latitude IS NULL or source.Longitude IS NULL)
THEN DELETE
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]

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
THEN
INSERT (Instance, InstanceID, Latitude, Longitude, StreetAddress)
VALUES ('Orion.Nodes', source.NodeID, source.Latitude, source.Longitude, source.[Description])
;
Cancel
Vote Up 0 Vote Down

Cancel