SWSQL or TSQL to Delete a Node from Solarwinds Tables when the Node is no longer in Active Directory

I am trying to find a way to delete a node from Solarwinds, using Powershell, TSQL or SWSQL. I would download a list of Nodes in Active Directory that are currently joined to the domain. This would allow Solarwinds to stop monitoring, and alerting, on Nodes that have been removed from the Domain. This is a common problem for us since we use Solarwinds to monitor our Public PCs at the Columbus Library. We are using NPM 12.2

Find more posts tagged with

tsql

Node

orion

delete

Database

npm

script

Accepted answers

tdanner

Powershell would be a reasonable tool for this job. At a high level, you would want to export the node list from NPM like this:

$swis = Connect-Swis # your connection details here

$npmNodes = Get-SwisData $swis "SELECT Caption, DNS, IPAddress, Uri FROM Orion.Nodes" # maybe add a WHERE clause to exclude network devices from this process

$adNodes = # get your list of domain members somewhere

$nodesToDelete = $npmNodes |? { # filter out the $adNodes # }

$nodesToDelete |% { $_.Uri } | Remove-SwisObject $swis

This is off the cuff and untested and I wimped out on writing the code to actually compute the set difference. Hopefully this is enough to get you started.

All comments

tdanner

Powershell would be a reasonable tool for this job. At a high level, you would want to export the node list from NPM like this:

$swis = Connect-Swis # your connection details here

$npmNodes = Get-SwisData $swis "SELECT Caption, DNS, IPAddress, Uri FROM Orion.Nodes" # maybe add a WHERE clause to exclude network devices from this process

$adNodes = # get your list of domain members somewhere

$nodesToDelete = $npmNodes |? { # filter out the $adNodes # }

$nodesToDelete |% { $_.Uri } | Remove-SwisObject $swis

This is off the cuff and untested and I wimped out on writing the code to actually compute the set difference. Hopefully this is enough to get you started.

wickedoz

Here is what I ended up with:

# This Script will Create a list of SolarWinds Windows machines matching name and IP criteria

# Must have Orion SDK and Active Directory Tools Installed

Add-PSSnapin SwisSnapin

Import-Module ActiveDirectory

# The line below will add credentials to an encrypted file

# GET-CREDENTIAL –Credential "SolarWinds” | EXPORT-CLIXML C:\Scripts\SecureCredentials.xml

$MyCredentials=IMPORT-CLIXML C:\Scripts\SecureCredentials.xml

$OrionServer = 'OrionServerName'

# Connect to Solarwinds

$swis = Connect-Swis -Credential $MyCredentials -host $OrionServer

# Query a List of Computer Names

$NPM_Nodes = Get-SwisData $swis "SELECT SysName FROM Orion.Nodes WHERE [Vendor] = 'WINDOWS' AND (IP_Address LIKE '172.16.%' OR IP_Address LIKE '192.168.%' OR IP_Address LIKE '10.73.%') AND SysName <> 'DLS' AND SysName NOT LIKE '%NVR%'"

# Query a List of Computer Names From Active Directory

$AD_Nodes = Get-ADComputer -Filter {Enabled -eq $True -and Name -notlike '*DED*' -and Name -notlike '*SHR*' } -SearchBase "OU=Computers,DC=xyz,DC=company,DC=org" | SELECT Name -ExpandProperty Name

# List Computers in SolarWinds that are not in Active Directory (No longer Joined to the domain)

$NodeDelete = $NPM_Nodes | ?{$AD_Nodes -notcontains $_}

# Get the URI Property for each node to be deleted from SolarWinds

$NodeDeleteURI = Get-SwisData $swis "SELECT Uri FROM Orion.Nodes WHERE SysName IN @NodeDelete" @{ NodeDelete = $NodeDelete }

# Delete the Node From Solarwinds

$NodeDeleteURI | Remove-SwisObject $swis