16 Replies Latest reply on Dec 1, 2016 5:04 PM by tdanner

    Query nodes based on custom property

    clum09

      Hello,

       

      Is there any way to query the Orion nodes with all the custom properties?

       

      Thanks.

      CluM09

        • Re: Query nodes based on custom property
          danofa

          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

            • Re: Query nodes based on custom property
              clum09

              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.

                • Re: Query nodes based on custom property
                  danofa

                  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

                    • Re: Query nodes based on custom property
                      tdanner

                      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.

                        • Re: Query nodes based on custom property
                          clum09

                          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?

                            • Re: Query nodes based on custom property
                              e.solonin

                              I may be wrong, but here

                              clum09 написал(а):

                               

                              Get-SwisData $swis "Select * FROM NodesCustomProperties cp INNER JOIN NodesData nd on cp.NodeID = nd.NodeID"

                               

                              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.

                                • Re: Query nodes based on custom property
                                  clum09

                                  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

                                   

                                  Thanks.

                                    • Re: Query nodes based on custom property
                                      tdanner

                                      Use "Orion.NodesCustomProperties", not just "NodesCustomProperties".

                                        • Re: Query nodes based on custom property
                                          clum09

                                          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?

                                           

                                          Thanks.

                                            • Re: Query nodes based on custom property
                                              KMSigma

                                              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.

                                                • Re: Query nodes based on custom property
                                                  clum09

                                                  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 : Source entity [NodesData] not found in catalog

                                                  At line:1 char:1

                                                  + Get-SwisData $swis "Select [Caption],[City] FROM Orion.NodesCustomProperties cp  ...

                                                  + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                                      + CategoryInfo          : InvalidOperation: (:) [Get-SwisData], FaultException`1

                                                      + FullyQualifiedErrorId : SwisError,SwisPowerShell.GetSwisData

                                                   

                                                  How do I test the SWQL Query in the SWQL Studio?

                                                   

                                                  Thanks.

                                                    • Re: Query nodes based on custom property
                                                      KMSigma

                                                      This is my complete script for pulling all node custom properties (also including the Caption and IP Address from the Nodes Table)

                                                       

                                                      ###############################
                                                      # Query-Swis.ps1
                                                      ###############################
                                                      #region Login Variable
                                                      $Hostname = "10.1.1.1" # Hostname (or IP) of the SolarWinds Server
                                                      $Username = "admin"
                                                      $Password = "MyComplexIfPlainTextPassword"
                                                      $SecPass  = ConvertTo-SecureString -String $Password -AsPlainText -Force
                                                      $Creds    = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $Username, $SecPass
                                                      #endregion Login Variable
                                                      #region Check for SolarWinds Snapin
                                                      if ( -not ( Get-Command -Name Connect-Swis -ErrorAction SilentlyContinue ) )
                                                      {
                                                          Add-PSSnapin -Name SwisSnapin -ErrorAction Stop
                                                      }
                                                      #endregion Check for SolarWinds Snapin
                                                      # Connect to SWIS
                                                      $SwisConnection  = Connect-Swis -Hostname $SwisHost -Credential $Creds
                                                      #region Get the SWIS Data
                                                      <#
                                                      Because the SWQL interface does not support 'SELECT * ...', we need to enumerate the custom properties that are applied to Nodes.
                                                      #>
                                                      $SwqlForCustomProperties = "SELECT [Field] FROM Orion.CustomPropertyUsage WHERE [Table] = 'NodesCustomProperties'"
                                                      $CustomProperties = Get-SwisData -SwisConnection $SwisConnection -Query $SwqlForCustomProperties
                                                      <#
                                                      Now that we have the list custom properties, we can add them to the query for more detail
                                                      #>
                                                      $SwqlQueryForDevices = "SELECT [Caption], [IP_Address], [$( $CustomProperties -join '], [' )] FROM Orion.NodesCustomProperties cp INNER JOIN Orion.Nodes nd on cp.NodeID = nd.NodeID"
                                                      
                                                      
                                                      Get-SwisData -SwisConnection $SwisConnection -Query $SwqlQueryForDevices
                                                      #endregion Get the SWIS Data
                                                      

                                                       

                                                      This queries SWIS and does 2 things:

                                                      1) Asks for the names of the custom properties (Field) that are assigned to nodes.

                                                      2) Queries for those fields and two others from the Nodes table (Caption, IP Address)

                                                      • Re: Query nodes based on custom property
                                                        bluefunelemental

                                                        If you nickname the table do you need to add that prefix to your columns?

                                                        Select cp.city from ....

                                                         

                                                        Thanks,

                                                        Christian

                                                          • Re: Query nodes based on custom property
                                                            KMSigma

                                                            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.

                                                              • Re: Query nodes based on custom property
                                                                tdanner

                                                                > 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".

                                      • Re: Query nodes based on custom property
                                        grantallenby

                                        What was the end goal?