9 Replies Latest reply on Jun 29, 2015 7:39 AM by dusting

    Query works in SWQL but not in SWIS

    dusting

      Hey all,

       

      I been trying to build a powershell script using Orion SDK that finds all IP addresses that are available between two subnets.

       

      When I create the query in SWQL studio it works!

      SELECT IPAddress, DnsBackward, DhcpClientName FROM IPAM.IPNode I Where Status=2 AND (I.Subnet.DisplayName = 'SUBNET1' OR I.Subnet.DisplayName = 'SUBNET2')
      

       

      But when I use "get-SwisData" it fails and I get a big array that contains "PscxDynamicType0" over and over. Below is my script and the array I am trying to walk through is "$objSwisData".

       

      #Region PSSnapin presence check/add
      if (!(Get-PSSnapin -Name "SwisSnapin" -ErrorAction SilentlyContinue))
      {    
          Add-PSSnapin SwisSnapin -ErrorAction SilentlyContinue
      }
      #EndRegion
      $target="Server"
      $swis = Connect-Swis -host $target -UserName "username" -Password "password"
      
      $objSwisData = Get-SwisData -SwisConnection $swis -Query "SELECT IPAddress, DnsBackward, DhcpClientName FROM IPAM.IPNode I Where Status=2 AND (I.Subnet.DisplayName = 'SUBNET1' OR I.Subnet.DisplayName = 'SUBNET2' )"
      
      foreach($nodeSwisData in $objSwisData){
        $counter++
      
        Write-host $nodeSwisData.IPAddress
        }
      }
      

       

       

      Let me know if anyone has run into something similar.


      Thanks,

       

      Dustin

        • Re: Query works in SWQL but not in SWIS
          tdanner

          A collection of "PscxDynamicType0" objects is normal. Get-SwisData creates powershell objects at runtime with properties that match whatever columns get returned by the swis query.

           

          When you say that it "fails", what error are you seeing?

            • Re: Query works in SWQL but not in SWIS
              dusting

              When I assign my query to the array

               

              objSwisData = Get-SwisData -SwisConnection $swis -Query "SELECT IPAddress, DnsBackward, DhcpClientName FROM IPAM.IPNode I Where Status=2 AND (I.Subnet.DisplayName = 'SUBNET1' OR I.Subnet.DisplayName = 'SUBNET2')"

               

              The array/object gets a Type name of PscxDynamicType0 and 3 properties that I selected in my query IPAddress, DnsBackward, and DhcpClientName.

               

               

              When I loop through these objects in the array I will get 50 of the same IP before it move to the next. When I query from SWQL Studio I only see 1 IP per line where I see 50 of the same IPs when looping through the foreach.

               

              Here is my example. You can see the $objSwisData comes with a collection of PscxDynamic objects with the count of 1862  and it should be 38.

               

               

              Also,

               

              I have done this same task with other queries with swis and have not seen this issue.

               

              Thanks,

               

              Dustin

                • Re: Query works in SWQL but not in SWIS
                  tom.rybka

                  How do you authenticate in SWIS in PS and in SWQL? Are you using an account (user/password) or the Orion certificate?

                    • Re: Query works in SWQL but not in SWIS
                      dusting

                      Both with same account. I am getting data but it is just repeated 50x.....

                        • Re: Query works in SWQL but not in SWIS
                          tom.rybka

                          Can you please grab SWIS log files in the debug message level for me to look at? You can change the logging level for SWIS using the LogAdjuster tool installed with Orion (changing the SWIS / Information Service 3.0 category is sufficient).

                           

                          Having changed the logging level, please grab the log file (typically C:\ProgramData\Solarwinds\InformationService\v3.0\InformationService.log) after executing the query in SWQL and after executing the PS script. Also, please, restart the SWIS 3.0 service before executing the query to avoid applying cached query plan in SWIS.

                            • Re: Query works in SWQL but not in SWIS
                              dusting

                              Tom,

                               

                              Since we will be restarting the service it affect production systems so we will have to do this early in the morning. Also, we created a case #819907 since we will be sending logs and would rather avoid posting to the message board.

                               

                              Thanks,

                               

                              Dustin

                                • Re: Query works in SWQL but not in SWIS
                                  tom.rybka

                                  Ok, makes sense, I will make sure I get to the data.

                                   

                                  One more suggestion - the restart is not necessary if you make sure that there is no cached query plan to be used. One trick to do that is: execute a query with added multiple whitespace characters in a query, so the query text is unique (no such query text has been sent to SWIS yet). This way you can achieve the same effect of complete query processing as with the restart.

                      • Re: Query works in SWQL but not in SWIS
                        muralikvp

                        Try to use this below query

                        ==========================

                        SELECT IPAddress, DnsBackward, DhcpClientName FROM IPAM.IPNode I inner join IPAM.GroupNode g on (i.Subnetid = g.Groupid)

                        Where i.Status=2 AND (g.FriendlyName = '<Your Friendly Name>' OR g.FriendlyName = '<Your Friendly Name>') and Distance =1

                        ======================

                         

                        Replace <Your Friendly Name> with your subnet names.

                         

                        Earlier your query has entity IPAM.IPNode which will create duplicate values of IPAddress with no of accounts in IPAM. That means if you have 5 accounts in IPAM then 5 IPAddress with same value will be duplicated instead of having one.

                         

                         

                        This kind of behavior for an entity IPAM.IPNode will be handled programmatically. For customer usage one should add inner join IPAM.GroupNode in where condition to avoid such duplicates in result.

                        1.png

                        While connecting SWQL studio try to select 'Orion (v3)' in servertype option. Orion (v2) is depreciated.Feel free to ask if you have any clarifications on above mentioned query