SQL with SWQL variable comparrasion

Hello everyone,

I`m trying to build custom alert for BGR relations and I have problem with custom dynamic message in 'trigger actions' tab.

I need to compare data from SWQL [SwisEntity] with IP address from Nodes table [SQL] and then re-translate it to Node name. My current message is below and the problem is highlighted.

On device ${N=SwisEntity;M=Router.Nodes.DisplayName} is BGP neighbor ${NeighborIP} unfunctional. Neighbor name: ${SQL: SELECT * FROM Nodes.Caption WHERE Nodes.IP_Address = '${N=SwisEntity;M=NeighborIP}'}

Yet, I`m not getting any other message then this: Neighbor name: ${SQL: SELECT Nodes.Caption FROM Nodes WHERE Nodes.IP_Address = 'xxx.xxx.xxx.xxx'}

I have tried escape characters, putting it in brackets, double quotation marks and single ones and many other things. If I remove this quotation marks, it is not able to load whole IP, it will stop at half.

Any suggestions please?

Thanks in advance,
Malisek

Find more posts tagged with

SWQL

npm

Alerts

sql query

Accepted answers

james.kluza

Ah looks like you used a ':' instead of ';' after SQL:

[quote userid="313006" url="~/products/server-application-monitor-sam/f/forum/103481/sql-with-swql-variable-comparrasion/325813"]${SQL: [/quote]

This should work though for SWQL:

**UPDATED**

${N=SWQL;M=SELECT Caption from Orion.Nodes WHERE IPAddress = '${N=SwisEntity;M=NeighborIP}'}

All comments

james.kluza

In your example:

[quote userid="313006" url="~/products/server-application-monitor-sam/f/forum/103481/sql-with-swql-variable-comparrasion"]Neighbor name: ${SQL: SELECT * FROM Nodes.Caption WHERE Nodes.IP_Address = '${N=SwisEntity;M=NeighborIP}'}[/quote]

You can't use '*' when creating a custom variable, you can only return a single column and result. Plus, the query itself is incorrect. You need something like:

${SQL: SELECT Nodes.Caption FROM Nodes WHERE Nodes.IP_Address = '{N=SwisEntity;M=NeighborIP}'}

Also, I don't see the reason to mix SQL/SWQL here. The Caption would also be available via the Orion.Nodes table in SWQL as well.

malisek

Thank you. I have tried it, but i returns this: Neighbor name: ${SQL: SELECT Nodes.Caption FROM Nodes WHERE Nodes.IP_Address = '{N=SwisEntity;M=NeighborIP}'}

The reason why I`m mixing SQL and SWQL together is that I couldn`t find any SWQL or table from wich is SWQL getting it`s variables. I need to re-translate neighbor IP to node name, which contains that IP. I didn`t find any built-in variable, which contains this value, so I had to think abut manual re-translation.

I was able to find this on SolarWinds manual: https://documentation.solarwinds.com/en/success_center/orionplatform/content/core-orion-variables-and-examples-sw1115.htm

If you have any SWQL code, which would do that re-translatition, I would be happy to have it from you. Or if you have any SWQL learning manuals / videos, I`m ready to learn it.

Thank you for your time and help

Malisek

james.kluza

Ah looks like you used a ':' instead of ';' after SQL:

[quote userid="313006" url="~/products/server-application-monitor-sam/f/forum/103481/sql-with-swql-variable-comparrasion/325813"]${SQL: [/quote]

This should work though for SWQL:

**UPDATED**

${N=SWQL;M=SELECT Caption from Orion.Nodes WHERE IPAddress = '${N=SwisEntity;M=NeighborIP}'}

james.kluza

Disregard, SQL does use ':' when you use variable builder. Regardless, the SWQL variable should work for you.

malisek

That worked. Thank you very much.

Have a nice weekend,

Malisek