Help with a simple SWQL query in the alert action message

sw44

Hi 

In the alert message (alertmessage) I would like to achive the following for volumes. 

If it is a Windows machine, use the "Device ID" property of the volume. 

If is Linux machine use the "caption" property of the volume. 

So, I've tested a query in the swql studio and is seems fine. But when I try to simulate it (with the simulate button) inside the alert, it shows nothing.  

This is the query in the alert:

${N=SWQL;M=SELECT case when n.Vendor like '%win%' then DeviceId ELSE v.caption end as vol

FROM Orion.Volumes as v INNER JOIN Orion.Nodes as n on v.NodeID = n.NodeID}

can someone help me to achive my goal in the alert? 

Thanks

Seashore

Hi
You need some sort of WHERE-part, so you only get the value for the volume you are alerting about. 
In below example I have added that, taking in the variable for the volumeID that the alert is about.

Also, you don't need to do an "INNER JOIN" with SWQL as it already know how those two tables are to be joined. Just reference them by the "path" as I have done below:

${N=SWQL;M=SELECT CASE WHEN V.Node.Vendor = 'Windows' THEN DeviceId ELSE v.caption END AS vol FROM Orion.Volumes AS v WHERE v.VolumeID=${N=SwisEntity;M=VolumeID}}

A note, the result from a SWQL or SQL variable in alert action can only return one row.