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

Escaping ${} in email alerts to do a SWQL REPLACE - I can do this in SWQL explorer but not email alerts.

So essentially I'm trying to pull the URL stored in componenttemplatesettings, use a SWQL replace statement to overwrite the ${IP} section with the IP of the node, and incorporate that into my alert for HTTP monitors. Here's the gist of the code - I'm unsure if this will work exactly but it does work in principle in SWQL explorer. I can pull the URL from ComponentTemplateSettings, and replace the ${IP} value with the IP of the node. Great.

SELECT
REPLACE(CTS.Value, '${IP}', (
SELECT A.Node.IP_Address
FROM Orion.APM.Component
JOIN Orion.APM.Application A ON A.ApplicationID = Component.ApplicationID
WHERE Component.ComponentID = 1
) as whatever
FROM Orion.APM.Component AS Component
JOIN Orion.APM.ComponentTemplateSetting CTS ON CTS.ComponentTemplateID = Component.TemplateID
WHERE Component.ComponentID = 1 AND CTS.Key = 'url'

The issue is that when attempting to use this in an email alert, using that ${IP} doesn't really work because this is wrapped in a ${N=SWQL;M=} block. I can replace IP, but I can't replace ${IP}, because the braces are messing with the statement. Is there any way to accomplish what I'm trying to achieve, any escape character I can use?

Find more posts tagged with

Accepted answers

All comments

adam.beedell

I'm struggling to get my head around what this is at the moment, that said couple possible directions to go:

1)

[quote userid="274280" url="~/product-forums/the-orion-platform/f/alert-lab/94234/escaping-in-email-alerts-to-do-a-swql-replace---i-can-do-this-in-swql-explorer-but-not-email-alerts"]using that ${IP} doesn't really work because this is wrapped in a ${N=SWQL;M=}[/quote]

You can evaluate a macro in a ${N=SWQL...} for sure, what I recommend is before you try and make the ${N=SWQL...} macro use the UI to select an IP address macro that evaluates the full format, then copy that inside the ${N=SWQL...} macro. The formatting changes based on what I'll call the home table. Similar to how components wont have an IP, but component.application.node will have an IP having travelled 2 layers up.

2)

Instead of using a macro here pull it from within the query. eg:

select top 10 c.Application.Node.IP
from orion.apm.Component c

marcrobinson

I am not sure if you need swql to do this. This custom variable will return the node ip:

${N=SwisEntity;M=Application.Node.IP_Address}

BigBeard

So to make what I'm trying to do more clear, I posted an image of the alert I developed now.

My Alert is pulling a URL from Orion.APM.ComponentTemplateSetting. This is pulling the URL field you can see documented here HTTP Monitor (solarwinds.com). I wanted to do this so I could create one HTTP/HTTPS alert that could work for all nodes, which informed the end user of the actual URL being monitored, which was efficient and easy to maintain.

The last hurdle I'm running into is that by default this field contains "http://${IP}:${Port}/". This is rather awkward, I would like the alert to contain a link the alert consumer could just click on. So what I need is to do a REPLACE to replace the ${IP} or ${Port} field with the corresponding IP or Port.

I'm trying to do that from within the alert action right now using a SWQL REPLACE, but where I'm having a problem is when I'm configuring the alert action, to get SWQL to treat ${IP} and ${Port} as string literals within the replace statement. I've tried backslashes, brackets, quotes, backticks, nothing works as an escape character within the Alert Action. So my question is how it would be possible to do this.

If I can't figure this out, I'll just devise some means to change the values within Orion.APM.ComponentTemplateSetting itself, such as by scheduling a SQL job. I would just rather avoid this, as it means the application monitor will break if the IP address of the node being monitored changes. Still this is the best solution I can see.

marcrobinson

Ok, this got me thinking. I worked out the ip, but never bothered scripting the port. I will drop some swql with the variables formatted for a component alert.

--Returns the component url that is tested
${N=SWQL;M=SELECT  cts.Value 
FROM  Orion.APM.ComponentTemplateSetting cts 
JOIN Orion.APM.Component ac 
    ON cts.ComponentTemplateID = ac.TemplateID 
Where ac.ComponentID = ${N=SwisEntity;M=ComponentAlert.ComponentID} AND cts.key =  'Url'}

--This gives you the node ip and port
${N=SwisEntity;M=Application.Node.IP_Address}:${N=SWQL;M=SELECT  cts.Value 
FROM  Orion.APM.ComponentTemplateSetting cts 
JOIN Orion.APM.Component ac 
    ON cts.ComponentTemplateID = ac.TemplateID 
Where ac.ComponentID = ${N=SwisEntity;M=ComponentAlert.ComponentID} AND cts.key =  'PortNumber'}

- The above with html formatting wrapped around it.
Please use the following link: 
 http://${N=SwisEntity;M=Application.Node.IP_Address}:${N=SWQL;M=SELECT  cts.Value 
FROM  Orion.APM.ComponentTemplateSetting cts 
JOIN Orion.APM.Component ac 
    ON cts.ComponentTemplateID = ac.TemplateID 
Where ac.ComponentID = ${N=SwisEntity;M=ComponentAlert.ComponentID} AND cts.key =  'PortNumber'}/