9 Replies Latest reply on Aug 8, 2017 2:44 PM by mwb

    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