2 Replies Latest reply on Sep 28, 2012 5:10 PM by t money

    Unable to do a Node Query by IP Address to get the NodeID

    t money

      We are trying to conduct a simple query via Powershell in which we query a device by IP address and it returns with the NodeID. We can query by Node ID and it returns with the IP Address, but we are not able to query by the Ip Address and it return the node ID. This works using SWQL Studio using the following query SELECT Nodes.NodeID FROM Orion.Nodes Where IP = '10.0.0.1'  but does not work using a powershell script.

      We have in our scripts ;

       

      The Script/Statement that Does Not Work

      Get-SwisData $swis 'SELECT Nodes\.IP_Address FROM Orion\.Nodes where Nodes.IPAddress='10.0.0.1''

       

      This Works

      Get-SwisData $swis 'SELECT Nodes\.IP_Address FROM Orion\.Nodes where Nodes.NodeID=55'

      10.0.0.1

       

      NodeID is System.Int32 and the IP_Address is System.String we are wondering how much of a difference this makes.

       

      Error

      PS C:\Documents and Settings\Administrator> & 'C:\Powershell Test Scripts\NodeQuery.ps1'

      Get-SwisData : no viable alternative at input '.' in Where clause

      At C:\Powershell Test Scripts\NodeQuery.ps1:17 char:13

      + Get-SwisData <<<<  $swis 'SELECT Nodes\.IP_Address FROM Orion\.Nodes where Nodes.IPAddress=10.0.0.1'

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

          + FullyQualifiedErrorId : SwisError,SwisPowerShell.GetSwisData

       

      Also tried the query like

      Get-SwisData $swis 'SELECT Nodes.NodeID FROM Orion.Nodes Where IP = '10.0.0.1''

      Get-SwisData : Cannot bind parameter 'Parameters'. Cannot convert the "10.0.0.1" value of

      "System.Collections.Hashtable".

      At C:\Powershell Test Scripts\NodeQuery.ps1:20 char:13

      + Get-SwisData <<<<  $swis 'SELECT Nodes.NodeID

          + CategoryInfo          : InvalidArgument: (:) [Get-SwisData], ParameterBindingExcepti

          + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,SwisPowerShell.GetSwisData

       

        • Re: Unable to do a Node Query by IP Address to get the NodeID
          tdanner

          I think you are running into trouble with nested quotes. Your query is wrapped in single quotes to make a PowerShell string. Then inside that, the IP address is also wrapped in single quotes to make a SWQL string. But that confuses PowerShell, since it sees the single quote character before the IP address as the end single quote for the query.

           

          Fortunately, PowerShell will let you use either single or double quotes for strings. If you write the statement like this, it should work:

           

          Get-SwisData $swis "SELECT Nodes.IPAddress FROM Orion.Nodes where Nodes.IPAddress='10.0.0.1'"


          Also, you don't need to put backslash before the periods.

           

          If you want to use query parameters, the syntax looks like this:

           

          Get-SwisData $swis "SELECT Nodes.IPAddress FROM Orion.Nodes where Nodes.IPAddress=@ip" @{ip='10.0.0.1'}