Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Populate a node's latitude and longitude on the world map when a custom property is inserted/modified

Hi,

I need help in understanding if the above can be done with the SDK.

Basically when a node sees its location custom property modified, it should be inserted in the world map (or edited if already there) with the coordinates for the location inserted (which are stored in a sql table on a separate db on the same instance).

I think this can be accomplished with sql triggers, but if possible to do with the SDK I would greatly prefer it.

Thanks

Find more posts tagged with

Accepted answers

All comments

KMSigma.SWI

The SDK doesn't know anything about other databases, which means it can't read the information from your source database.

LDave

So with the SDK you can't script something / sql querying something in a custom way?

I think that potentially it can be done with alerts too, executing custom scripts.

KMSigma.SWI

No, you can totally query (nearly) anything in the SolarWinds space. You said the coordinates were stored in another database on the same instance. The API doesn't know anything about that other instance. It only knows about data in the ones the SolarWinds server uses.

That said, you could do this with a combination of work.

Query the Database for coordinates using traditional SQL calls (outside of the SolarWinds API, but still in a script)
Query the SolarWinds Platform API about the matching nodes (within the API)
Send update commands as needed (via the API)

LDave

Ok, and is it possible to trigger the script on a custom property value change?

bobmarley

If you don't need it to run it within Orion, but as a scheduled task or using your automation tool of choice it can be done with the API. This doesn't address only the ones that are changed, but syncs up all of the nodes from the external source with the nodes in Orion.

First have your script pulls all of the node information you want from the external SQL database (Invoke-Sqlcmd). We use IP Address as a key to link the tables to the Orion database later. All of this info is written out to a .csv file. The data we collect is IP Address, Node Name, Street Address, City, Node Status, etc.

Our script then changes any data we want within the .csv file to eliminate items like nodes that are not 'in service', nodes we intentionally skip, etc. The script then writes out new headers to the .csv file the match exactly the Custom Properties.

The last thing the script does is import the new custom properties from the .csv file using IP Address as the key.

If you are familiar with Powershell this isn't too hard to put into place.

Here are some code examples to get you started.

Getting data from your external DB

CLS
cd c:\powershellscripts
Write-Host "retreiving inventory from External SQL Database"
Invoke-Sqlcmd -Query "YOURQUERY" -ServerInstance "YOURSQLSERVER" -Database "YOURDATABASENAME" |Out-File -FilePath C:\PowershellScripts\inventory.csvCode

Then massage the data in any way needed, write headers into your .csv file and output the data to a file called ImportFile.csv

In the example shown below we use these: NodeName NodeIPAddress Group Severity Application as the headers in the Import.csv file

Modify the script below to include your specific custom properties. This will import the 'new' custom properties in for your nodes.

##### Notes
#Make sure the ImportFile.csv file has at least these headers NodeName NodeIPAddress Group Severity Application
#Group Severity Application are Custom Properties, remove and replace these with the ones from your environment
#Items in ALL CAPS will need to be changed to represent your environment
#Path and filenames will need to match your environment


cls
cd c:\powershellscripts
Write-Host "Loading Solarwinds Plug in"
Import-Module -Name SwisPowerShell
Add-PSSnapin SwisSnapin 
Write-host "This script will import custom properties from C:\PowershellScripts\ImportFile.csv"
$swis = Connect-Swis -host YOURORIONSERVER -Trusted

Write-Host "Reading data from C:\PowershellScripts\ImportFile.csv"

$csv = Import-Csv C:\PowershellScripts\ImportFile.csv
$csv | ForEach-Object {

    $NodeName = $_.Nodename
    $NodeIPAddress = $_.NodeIPAddress
    $Application = $_.Application
    $Severity = $_.Severity
    $_Group = $_._Group


#retrieving the nodeID from the database       
$query = "

    SELECT NodeID FROM Orion.Nodes Where IPAddress Like '$NodeIPAddress'    

"

$rows = Get-SwisData -SwisConnection $swis -Query $query

Write-host "$rows"

$CustomProps_Application = @{  Application = "$Application";  }
$CustomProps_Severity = @{  Severity = "$Severity";  }
$CustomProps_Group = @{  Group = "$Group";  }

$UID = $rows

$uris = Get-SwisData $swis "SELECT Nodes.CustomProperties.Uri FROM Orion.Nodes WHERE NodeID LIKE '$UID'"

$uris | Set-SwisObject $swis -Properties $CustomProps_Application
$uris | Set-SwisObject $swis -Properties $CustomProps_Severity
$uris | Set-SwisObject $swis -Properties $CustomProps_Group


}



Exit (0)

adam.beedell

Use the latitude and "longitude (world map)" variables rather than a custom one to cut the complexity.

Script with the SDK to read your source SQL, then loop a query for all devices that match whatever's stored in your DB, returning the SWID
then loop through the SWIDs updating properties including lat and long.