Hello,
Is there any way to query the Orion nodes with all the custom properties?
Thanks.
CluM09
Hi,
using the REST api you can perform a read on the node swis URI like so..
swis://hostname/Orion/Orion.Nodes/NodeID=1/CustomProperties
which will return all the possible custom properties and their values
more info is available here: REST · solarwinds/OrionSDK Wiki · GitHub
What was the end goal?
danofa,
Thank you for the quick response. I am sorry for being late in responding back to you.
Actually, I need to query all the nodes from SolarWinds including built-in and custom properties. I need this information to true up the SolarWinds inventory for my company.
I would assume that the syntax you provided is for one node only with a known NodeID. I am not familiar with SolarWinds REST api, and I have never used it. Do I need to use SwisSnapin snapin or I just use the Invoke-Webrequest or Invoke-RestMethod cmdlet from PowerShell or the curl utility?
Please give the actual syntax of the REST api command to perform the operation.
Thanks again.
Ah, well that changes a few things,
maybe the best bet in your case is to make a custom report, based on a SQL query
then you can do something like the following...
Select * FROM NodesCustomProperties cp
INNER JOIN NodesData nd on cp.NodeID = nd.NodeID
this will give you all the custom properties and all the data that applies to each node... if its too much information you can whittle it down with a SELECT IP_Address, Vendor, <insert column name here>, cp.* FROM ...
Hope this helps
You don't need to use a SQL query. If you want to do this in the reporting system, you can make a report for nodes that includes whatever properties you need, including custom properties, just by picking them from the list.
If you prefer to do this from a script, you can use a Query request to get a complete list of nodes including custom properties. In PowerShell this is done with the "Get-SwisData" cmdlet. See the REST API documentation page (linked earlier in this thread) for information about how to run a SWQL query through that channel.
tdanner,
What is the syntax for the Get-SwisData cmdlet to pull the node data including the custom properties? I tried danofa's code, but it generated an error below:
Get-SwisData $swis "Select * FROM NodesCustomProperties cp INNER JOIN NodesData nd on cp.NodeID = nd.NodeID"
which results in this error: Get-SwisData : no viable alternative at input '*' in Select clause
Can you give an example on how to do this?
I may be wrong, but here
clum09 написал(а): Get-SwisData $swis "Select * FROM NodesCustomProperties cp INNER JOIN NodesData nd on cp.NodeID = nd.NodeID"
clum09 написал(а):
you use SWQL , not SQL. SWQL does not support Select * from syntax, you have to specify columns/fields you need as result.
===================
Common SQL Constructs Not Supported
SWQL does not support the following common SQL constructs:
Note: You must use CRUD operations to create, read, update, or delete entities.
SELECT * FROM … (You must list the actual properties you want to select.)
UPDATE, INSERT, DELETE, etc. (You can only use SWQL to read data.)
====================
So try something like this : Get-SwisData $swis "Select NodeID FROM NodesCustomProperties cp INNER JOIN NodesData nd on cp.NodeID = nd.NodeID" , if it does not show any error just add columns you need in Select part of your query.
I tried: Get-SwisData $swis "Select City FROM NodesCustomProperties cp INNER JOIN NodesData nd on cp.NodeID = nd.NodeID", but I got the following error:
Get-SwisData : Source entity [NodesCustomProperties] not found in catalog
Use "Orion.NodesCustomProperties", not just "NodesCustomProperties".
I tried using the Orion.NodesCustomProperties as you indicated, but it still does not work.
PS C:>Get-SwisData $swis "Select City FROM Orion.NodesCustomProperties cp INNER JOIN NodesData nd on cp.NodeID = nd.NodeID"
Get-SwisData : Source entity [NodesData] not found in catalog
At line:1 char:1
+ Get-SwisData $swis "Select City FROM Orion.NodesCustomProperties cp INNER JOIN N ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Get-SwisData], FaultException`1
+ FullyQualifiedErrorId : SwisError,SwisPowerShell.GetSwisData
Do you have any suggestion?
Syntax Error in the SWQL Statement Change it to:
SELECT [Caption], [City]
FROM Orion.NodesCustomProperties cp INNER JOIN Orion.Nodes nd on cp.NodeID = nd.NodeID
It looks like you blended straight SQL (SELECT * FROM [dbo].[NodesData]) and SWQL (SELECT * FROM Orion.Nodes).
Did you test the SWQL Query in the SWQL Studio before trying to run it in PowerShell? That's what I always do just to be safe.
I still get the error:
PS C:> Get-SwisData $swis "Select [Caption],[City] FROM Orion.NodesCustomProperties cp INNER JOIN NodesData nd on cp.NodeID = nd.NodeID"
+ Get-SwisData $swis "Select [Caption],[City] FROM Orion.NodesCustomProperties cp ...
How do I test the SWQL Query in the SWQL Studio?
If you nickname the table do you need to add that prefix to your columns?
Select cp.city from ....
Thanks,
Christian
I didn't and it worked.
I'm assuming that it follows the same logic as SQL - if there's a unique field name returned, you can just reference that field by name.
> I'm assuming that it follows the same logic as SQL - if there's a unique field name returned, you can just reference that field by name.
Yup, that's what it does. With an important exception: references to navigation properties properties must be qualified with the entity name or alias. So you can do "SELECT Caption FROM Orion.Nodes foo" and it works fine. But you can't do "SELECT Volumes.Status FROM Orion.Nodes foo" - it has to be "SELECT foo.Volumes.Status FROM Orion.Nodes foo".
Here is the equivalent SWSQL. This is pulling all nodes that have the name 'Cisco' and a custom property called Team that contains 'Telecommunications'
Select IP_Address, Caption, NodeID, _Group FROM Orion.NodesCustomProperties cp
INNER JOIN Orion.nodes nd on cp.NodeID = nd.NodeID
Where Caption Like '%Cisco%'AND Team Like 'Telecommunications'
