cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 12

Custom SQL Variable

If I write a custom SQL variable for the Advanced Alert Engine to use to populate information from more than one field in the database:

${SQL:select...}

... it seems to only report back the first value. 

Example:
${SQL:select NodeID, IP_Address, DNS from nodes where nodeid like '1' or nodeid like '2'}

This returns a value of '1'. 


We are trying to use this to populate information for multiple devices in a cluster when an alert fires one of the device in that cluster. 

Is this possible?


Thanks,
Jason Henson
Loop1 Systems
www.Loop1Systems.com

0 Kudos
4 Replies
Level 12

I'll show you all one example of my findings regarding SQL variables.

In below example I wanted to list all nodes that was depening on the down node. Adjust your query to your need.

${SQL: SELECT

     N.Caption+', '

FROM Nodesdata N

Inner join Dependencies D on N.nodeid=D.ChildNetObjectID

WHERE

     D.ParentNetObjectID=S{N=SwisEntity;M=NodeID}

     AND D.ParentEntityType='Orion.Nodes'

    AND D.ChildEntityType='Orion.Nodes'

FOR XML PATH ('')}

Adding the "FOR XML PATH" in the end gives me all values in one line (as orion only accepts one single cell back from the SQL varieble).

The "+', '" after N.Caption takes away the XML formating and gives you just a comma separated line.

What I also realise is that you can't add the variable looking like above, you have to add it as one line, like this:

${SQL: SELECT N.Caption+', ' FROM Nodesdata N Inner join Dependencies D on N.nodeid=D.ChildNetObjectID WHERE D.ParentNetObjectID=S{N=SwisEntity;M=NodeID} AND D.ParentEntityType='Orion.Nodes' AND D.ChildEntityType='Orion.Nodes'FOR XML PATH ('')}

Not as nice but otherwise it will not run the query.

Hope this helps someone!

Thank you so much for this! I used this to pull a list of volumes on a server into an alert email!

${SQL: SELECT Caption+', '
From Volumes
Where NodeID = ${N=SwisEntity;M=Node.NodeID}
FOR XML PATH ('') }

Never would have figured out how to pull in this info without your post.

0 Kudos
Level 13

You could do something like this:

declare @List NVARCHAR(4000)
select @List = COALESCE(@List + ', ' + convert(nvarchar(10), nodeid) + ' ' + caption, convert(nvarchar(10), nodeid) + ' ' + caption)  from nodes
where NodeID = 1 OR NodeID = 2
select @List

This returns one value as a string of concatenated text.  Obviously, change the format, where clause, and columns to your needs, but the concept is the same.

APM does something similar with their built-in SQL Functions:

apm_GetComponentStatusErrorDescription
apm_GetComponetsForApplication

You may consider creating your own function or stored procedure to pass in params on the fly.

0 Kudos
Level 15

It's kinda ugly, but if you put "FOR XML PATH" at the end of the query, it shoves everything into one value with XML formatting in it.

0 Kudos