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
