Include all node IP addresses in alert?

I've done some testing with a custom SQL query to try to get all IPs associated but it's only returning the first IP in the database:

     ${SQL:SELECT IPAddress FROM [dbo].[NodeIPAddresses] WHERE NodeID = ${NodeID}}

When I run this same query in the database manager it returns exactly what I'm looking for:

This is the first custom SQL query I've made so I'm sure I'm missing something. How can I get the alert manager query to include all entries in the result and not just the first one?

hi bhilgenkamp_botw

can you tell what type of alert are you trying to create so we can check the predefined variables that are available?

please include the type of property to monitor.. thanks!

The type of property is "Node" (specifically looking for all IPs on routers and switches).

It's by design. See my workaround for similar situation

Output last 25 syslog messages for node on Advanced Alert email

I thought this was going to work but unfortunately the email that is sent out only contains the first IP. Example:

Query in Database Manager:

Query in Alert Manager:

Result in email:

10.112.35.249 |

So basically the output in the db manager seems like this is something I can use, but unfortunately I'm still only getting the first IP and the pipe symbol. Did you have this issue at all, or any tips on how I could fix this?

It's due to how the alert looks at the variable.  I know another user was able to do something similar in Re: Help with SQL query in Alert.

Basically you have to do:

${SQL:Select (Column1 + char(9) ) FROM Table where NodeID = '${NodeID}'  FOR XML RAW(''), ELEMENTS}

Your SQL macro is correct. However, the screenshots is not an apples to apples comparison since 10.112.35.249 is not the list of IPs from your SQL output screenshot. Does the node with 10.112.35.249 indeed have multiple IPs?

You are correct, I must have picked the wrong node to test on the next time around. The macro is now working properly:

Is there any way to make a line return instead of the pipe symbol, so that each IP is on its own line?

char(9) in mharvey's example is a SQL line feed character, I think. You can try char(9) instead of |.

Disclaimer - i haven't tried this character before. Please report back what you find out

I tried char(9) but no go:

Macro

Result