SQL Substring not working for HTTP/S POST alerts

Question

I'm trying to use the SQL "Substring" function to grab a site code from a node custom property called "Site". This is then going to be used in an alert to post a MessageCard (HTTS/S POST) to a Teams channel.

A site name would be something like "BEN - Site Name" and I'm trying to extract the "BEN" characters.

Based on this post (Using SQL Substring command to string part of a variable to enter into a Custom Property - Forum - Network Performance Monitor (NPM) - THWACK (solarwinds.com)) I'm doing the following using SUBSTRING for the custom property:

{
 "@type": "MessageCard",
 "@context": "">schema.org/extensions",
"summary": "Store PoS down - ${SQL: Select Substring('${N=SwisEntity;M=CustomProperties.Site}',1,3)}",
 ""sections": [],

I need the "summary" of the message card to read "Store PoS down - BEN"

The error I'm getting is "No column name was specified for column 1"

I've tried many different variations found in web searches but nothing is helping.

What am I doing wrong?

Thanks!

infspt · Answer

Thanks for the reply. I'll check out your suggestions and see if I can make this work.

adam.beedell · Answer

You can evaluate the macros seperately by sticking them in an email action and using the simulate button

${N=SwisEntity;M=CustomProperties.Site} - Looks fine at a glance but looks aimed at a node alert, so if not that then maybe an issue.

${SQL: Select Substring('BEN something',1,3)} - Looks ok at a glance but there's some shortcuts happening there. The error sounds like it's referring to this. Add "as Splitsite" to give it a column name. There's also no FROM statement, which you may or may not be able to get away with. if not add a TOP 1 to the front and a FROM Orion.Nodes at the end or something, doesnt matter really.

Try those see how you get on