Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

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

Find more posts tagged with

Accepted answers

All comments

netlogix

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.

Karlo.Zatylny

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.

Seashore

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!

hdixon

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.