4 Replies Latest reply on Aug 25, 2016 12:15 PM by cstewart28

    PowerShell: Correct SQL Select Statement with LIKE

    cstewart28

      I'm trying to get my PowerShell select statement correct, I can get the correct response with the SWQL, but it does not work for me in the PS script.  I'm sure it's easy, but not sure what I'm missing. 

       

      SQL statement that works in SWQL Studio is:

       

      SELECT DefinitionID, ContainerID, Expression, Definition

      FROM Orion.ContainerMemberDefinition

      WHERE ContainerID='23' AND Expression LIKE '%362'

       

      Result 140

       

      In PowerShell

       

      $MemberPrimaryID = '362'

      $ContainerID = '23'

       

      $definitionID = get-swissdata $swis 'SELECT definitionID from Orion.ContainMemberDefinition Where ContainerID='$ContainerID' AND 'Definition' LIKE '%$MemberPrimaryID'"

       

      It runs with no issues, but it does not return a valid value.  With the SQL statement I get 140 as the result with PowerShell I get nothing..

        • Re: PowerShell: Correct SQL Select Statement with LIKE
          tdanner

          You've got something odd going on with the quotes around the query on the last line. It looks like the query starts with a single quote (') and ends with a double quote ("). That's not valid powershell, so it may just be a re-typing or copy/paste error.

           

          One possible cause: powershell only replaces $variables in double-quoted strings. In single-quoted strings the $ character is not special.

            • Re: PowerShell: Correct SQL Select Statement with LIKE
              cstewart28

                Around the SELECT it is a " not a single ' - typo on my part.

               

              I have tried putting single ' around the SQL table column names, does nothing, but using the ' around the $variables do work such as '$IP' that I have used, not sure why.

               

              I changed and put double quote " around the variables, but same result.

               

              I also tried using the value and not the variable LIKE "%362"" double quotes - nothing,

              I tried LIKE '%362' and get an error.

               

              In the SDK groups.ps1 example it shows LIKE 'Up%' " so I'm trying to adapt that part.

               

              Not sure what I'm missing here.

                • Re: PowerShell: Correct SQL Select Statement with LIKE
                  KMSigma

                  Isn't the ContainerID an integer?  Why are you doing the query as a string (ie. 23 vs '23')?

                   

                  I've got this working for me: (UN/PW/OrionServer removed)

                  $ContainerID = 1
                  $Expression  = "ADDS"
                  $SWQL = @"
                  SELECT DefinitionID, ContainerID, Expression, Definition
                  FROM Orion.ContainerMemberDefinition
                  WHERE ContainerID=$( $ContainerID ) AND Expression LIKE '%$( $Expression )%'
                  "@
                  if ( -not ( Get-Command -Name Get-SwisData -ErrorAction SilentlyContinue ) )
                  {
                      Add-PSSnapin -Name SwisSnapIn -ErrorAction SilentlyContinue
                  }
                  $SwisConnection = Connect-Swis -UserName "UserName" -Password "XXXXXXXX" -Hostname "Orion.FQDN"
                  Get-SwisData -SwisConnection $SwisConnection -Query $SWQL
                  
                  1 of 1 people found this helpful