11 Replies Latest reply on Apr 12, 2018 11:05 AM by tdanner

    REPLACE function for SWQL?

    mwb

      Anything available in SWQL that would approximate the REPLACE function in SQL?

       

      It's a long story, but this is the query I'm working with in SQL - basically a volume can fill up, but as long as all the same "opt<x>" volumes on a server don't fill up, it doesn't need to alert.

       

      The SQL works in a query and report, but I want to get a dashboard resource going with SWQL - but the REPLACE functionality doesn't work to allow me to replace the various digits on the various lengths of volume name.

       

      select Server, Volume, CAST(ROUND(Percentage,0) as varchar(10)) + ' %' as Percentage,
      CASE
      WHEN Used>=1099511627776
      THEN CAST(CAST( ROUND((Used/1024/1024/1024/1024),2) as DECIMAL(10,2)) as varchar) + ' TB'
      ELSE CAST(CAST( ROUND((Used/1024/1024/1024),2) as DECIMAL(10,2)) as varchar) + ' GB'
      END
       as Used,
      
      
      CASE
      WHEN Size>=1099511627776
      THEN CAST(CAST( ROUND((Size/1024/1024/1024/1024),2) as DECIMAL(10,2)) as varchar) + ' TB'
      ELSE CAST(CAST( ROUND((Size/1024/1024/1024),2) as DECIMAL(10,2)) as varchar) + ' GB'
      END
      
      
      
      as Size
      from
      (
      select Server, Volume, SUM(VolumeSpaceUsed) as Used, SUM(VolumeSize) as Size
      , (SUM(VolumeSpaceUsed) / SUM(VolumeSize)) * 100 as Percentage
      from
      (select n.Caption as server,
      CASE
      WHEN v.Caption like '/opt%'
      and v.Caption<>'/opt' 
      THEN 'opt'
      WHEN v.Caption like '%opt%'
      and v.Caption not like '/opt%'
      THEN  
      
      
         REPLACE(
            REPLACE(
              REPLACE(
                REPLACE(
                  REPLACE(
                    REPLACE(
                      REPLACE(
                        REPLACE(
                          REPLACE(
                            REPLACE(
                              v.caption,
                            '9', ''),
                          '8', ''),
                        '7', ''),
                      '6', ''),
                    '5', ''),
                  '4', ''),
                '3', ''),
              '2', ''),
            '10', ''),
          '1', '')
      
      
      
      
      ELSE v.Caption
      
      
      END as Volume
      
      
      , VolumeSpaceUsed, VolumeSize from Volumes v
      
      
      JOIN Nodes n
      on n.NodeID=v.NodeID
      
      
      where v.Caption like '%opt%'
      and v.Caption<>'/opt'
      and v.SilencedVolume<>1
      ) x
      
      
      GROUP by Server, Volume
      ) z
      
      
      where Percentage>=85
      
      
      
      
      Order by Percentage DESC, Server
      
        • Re: REPLACE function for SWQL?
          tdanner

          We don't currently have a REPLACE-equivalent function in SWQL, and unfortunately I don't see a way to accomplish this without one.

            • Re: REPLACE function for SWQL?
              mwb

              Well, technically CASE can be used to replace the volume names - it just won't have the dynamic properties in the same way.

               

              I'm already doing it with volume that I know are /opt<x> - it's just not as easy with volumes that may be /<x>opt<x> - because the first variable can be random strings of characters.

               

              So multiple CASE can certainly do it with a listing of all the possible prefix variables - it just won't update if there are new ones in the future.

               

              That may be the way that I go, I'll update here if I find another solution.

               

              Any thoughts as to add some more of these types of basic SQL functions to SWQL?  SWQL is very powerful and useful, but frustrating when I have to design a whole new query to work around the limitations such as no REPLACE, no PIVOT, etc - which really are still read and display functions when you get down to it.

                • Re: REPLACE function for SWQL?
                  tdanner

                  REPLACE is a very obvious one to add. I'll open a bug for that.

                   

                  PIVOT is a pretty complex SQL feature that changes the structure of the result set. It would definitely be useful, but it would take significant effort to add it to SWQL.

                    • Re: REPLACE function for SWQL?
                      mwb

                      Thanks!

                       

                      Yeah, PIVOT I've worked around with many multiple joins.

                       

                      This REPLACE I've worked around the worst offenders with about half dozen specific WHEN statements in my CASE function.

                       

                      Thanks for the quick feedback and seeing the value of the REPLACE.  Especially since you're formatting data for dashboards in those Orion Custom Query resources, I can see where it could be used in multiple ways.

                      • Re: REPLACE function for SWQL?
                        m-milligan

                        Did REPLACE ever get added to SWQL? It doesn't work for me.

                          • Re: REPLACE function for SWQL?
                            mwb

                            Not that I'm aware of, I'm still using CASE. Maybe tdanner can advise further.

                            • Re: REPLACE function for SWQL?
                              tdanner

                              No, this has not been implemented yet.

                                • Re: REPLACE function for SWQL?
                                  tdanner

                                  REPLACE is implemented in NPM 12.2 and the other Orion Platform 2017.3 releases (currently in beta).

                                    • Re: REPLACE function for SWQL?
                                      mwb

                                      Cool!  Thanks for the update!

                                      • Re: REPLACE function for SWQL?
                                        micahcox

                                        The "pattern" in the Replace function appears only to be literal expressions.  It doesn't seem to support percent, REGEX, or any kind of pattern other than literal characters of static length.  Given the lack of a string search function that returns a character index number, and no way to search for wildcards, there's a huge number of things that can't be accomplished.  I can't find a definition of "pattern" but my testing in the SDK seems to only reveal literals as the pattern.  Is there a wildcard, REGEX, or some other variable pattern matching mechanism in "pattern" for the Replace() function?  If not, please implement it or a substring search that returns an index so that more complex string functions can be completed.  In SQL I was able to split off the ifIndex value from an OID value that was the concatenation of the ifIndex and another value with a delimiter between, then do a JOIN and/or WHERE to return only table values.  I can't solve the same problem with SWQL because of the string limitation.

                                          • Re: REPLACE function for SWQL?
                                            tdanner

                                            Yes, it is searching for a literal expression. No wildcards or regex. This follows the behavior of the SQL "replace" function. For performance reasons it is best for us to stick to functionality that can be handled in SQL and not in our code.

                                             

                                            Orion Platform 2018.2 (currently in beta) adds the "charindex" function to SWQL. That should enable a few more string processing scenarios.