Custom SWQL Alert Condition - Comparing Volumes of 2 Nodes

Question

SELECT

E0.Node.Caption

, E0.Caption
FROM Orion.Volumes AS E0
WHERE E0.Node.Caption = 'Node1' AND E0.Caption = 'VolumeName'
AND (E0.VolumeSize
<
( SELECT E0.VolumeSize FROM Orion.Volumes as E0 WHERE E0.Node.Caption = 'Node2' AND E0.Caption = 'VolumeName' )) 
 In Orion SWIS Query, this query correctly pulls the node & volume if Node1's volumesize is less than Node2's volumesize (volumes have the same name across nodes). 
 However, when I put this into the custom SQL trigger condition, I am provided with "Query Condition is not valid". 
 
 This is an alert to alert on a scheduled clone job. If originating volume is consuming more than destination volumes have sized, clone will fail. 
 The end goal here is to build out alerts for when NodeA's volume has more space used than NodeB,C, D..etc's volumes have size/allocated. But, I figured I'd start with a simple size comparison to avoid mismatch issues. 
 
 Can anyone provide information/example on how to use the custom SWQL editor for trigger conditions in an alert? It seems it plays more finicky than normal SWQL builders within alerts/widgets. For starters, simply getting this script working in the conditions of an alert would be fantastic! If there is a better method to compare size vs space used between different nodes in SAM, I would take that as well.

Hinsy_Heimdall · Accepted Answer

adam.beedell Thank you for the reply. I'd verify your answer, but your select statement is still referening E0. I hadn't initially realized that the select statement it showed was actually a forced requirement, not an example/advisor. You got me down the right path. I've included the functioning (sanitized) comparison below. I am using the first section of WHERE statement as NODEB intentionally so that the alert is triggering on the child nodes that are comparing against the parent. Using the parent in the first half causes all of the alerts to flag against the parent node. 
 
 Query: Make sure to replace [VOLUMENAME], [NODEA], and [NODEB] with your related entities. (removing brackets as well) WHERE Volumes.Caption = '[VOLUMENAME]' 
AND Volumes.Node.Caption = '[NODEB]' 
AND Volumes.VolumeSpaceUsed < (SELECT Volumes.Size FROM Orion.Volumes AS Volumes WHERE Volumes.Caption = '[VOLUMENAME]' AND Volumes.Node.Caption = '[NODEA])

adam.beedell · Answer

Glad that's sorted 
 One small note for why I left in one set of E0's: 
 ( SELECT E0.VolumeSize FROM Orion.Volumes as E0 WHERE E0.Node.Caption = 'Node2' AND E0.Caption = 'VolumeName' ) 
 In the subquery, E0 is actually defined in the underlined bit - I was trying to show that Aliases are still accepted, but that you do need to define them and the first FROM is defined already with a specific name of some sort or the other

adam.beedell · Answer

I think you might be able to solve this with complex conditions using the checkbox on the trigger page. If not though; 
 
 If you're doing custom SQL (which should be almost a last resort), the top lines are predefined, so your query should start like this for the nodes table If you're doing SQL, you also cant use the autojoined dot format "E0.Node.Caption", that's SWQL 
 If you're doing SWQL, same thing with the prefedined SELECT and FROM statements: So your query should look closer to: WHERE Volumes.Node.Caption = 'Node1' AND Volumes .Caption = 'VolumeName' AND ( Volumes .VolumeSize < ( SELECT E0.VolumeSize FROM Orion.Volumes as E0 WHERE E0.Node.Caption = 'Node2' AND E0.Caption = 'VolumeName' ))

Custom SWQL Alert Condition - Comparing Volumes of 2 Nodes

Top Replies