MACRO SQL ERROR - Incorrect syntax near the keyword 'AS'.

I apologize in advance for the wall of info here... lol

EDIT: I see you have FOR XML PATH ('') at the end which I have not personally used but I believe is a possible way to concatenate multiple rows into a single field?

So you may want to take a look here to get an idea of how to structure a SQL query.

SQL is tough on its own without the odditities of SolarWinds macro variable and embedded variables as well. 

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15

Using the SQL macro variable is very powerful and offers a lot of flexibility but does need to adhere to T-SQL syntax. This query needs a lot of work before it will work for you, but SQL variables can only return 1 row result. There's "ways" to concat multiple fields or other data into html but I'm not aware of a way to return multiple rows.

Due to this it's usually good practice to use a SELECT DISTINCT TOP 1 as the first bit of the query. That way you're telling the query to only return the top 1st unique result.

That said you have as the error suggests, incorrect syntax near the AS  keyword.

• Issue1: Aliases need to be put directly after the resource referenced, e.g. SolarWindsOrion.dbo.StatusInfo AS [data()]
• Issue2: Multiple FROM keywords referencing 2 different source tables, one of these (ideally SolarWindsOrion.dbo.ContainerMemberSnapshots) should be the only FROM ... datasource, the SolarWindsOrion.dbo.StatusInfo should be INNER JOIN'ed in on specific criteria if needed
• Issue3: Multiple WHERE keywords, the rest should be turned into AND keyword statements or added to the INNER JOIN criteria

Try this built in group variable does this work for your needs? ${N=OrionGroup;M=GroupStatusRootCause}

(see https://www.solarwinds.com/documentation/en/flarehelp/orionplatform/content/core-group-variables-sw1124.htm for a list of other group variables)

Also this is a geeat reference: https://www.solarwinds.com/documentation/en/flarehelp/orionplatform/content/core-orion-variables-and-examples-sw1115.htm 

Hi, like what you trying to create but the query need some changes.

Below query should give you what you want:

SELECT 
M.FullName + ' (' + S.StatusName + ')' +', '
FROM ContainerMemberSnapshots AS M
INNER JOIN StatusInfo AS S on M.[Status]=S.StatusId
WHERE status != 0 AND status != 1 AND status != 9 AND status != 27 AND ContainerID = 1
FOR XML PATH('')

If above give you what you want below code is the "variable" to add in the alert email. 

${SQL: SELECT M.FullName + ' (' + S.StatusName + ')' +', ' FROM ContainerMemberSnapshots AS M INNER JOIN StatusInfo AS S on M.[Status]=S.StatusId WHERE status != 0 AND status != 1 AND status != 9 AND status != 27 AND ContainerID = ${N=SwisEntity;M=ContainerID} FOR XML PATH('')}

Hi SeaShore, yes this is what I want, but still setting an error in trying to run the code in the Alert message keep getting 

MACRO SQL ERROR - No column name was specified for column 1 of 'w02cs5dn_lwz'.  when I simulate it, but in my SQL Server Studio  it work great.  Any thoughts ? 

Ahh, my bad. Orion does not like if you have "unnamed columns" so we have to add a name for the column:

${SQL: SELECT M.FullName + ' (' + S.StatusName + ')' +', '  AS [N] FROM ContainerMemberSnapshots AS M INNER JOIN StatusInfo AS S on M.[Status]=S.StatusId WHERE status != 0 AND status != 1 AND status != 9 AND status != 27 AND ContainerID = ${N=SwisEntity;M=ContainerID} FOR XML PATH('')}

You probably now get some extra characters in the output. If you don't want them I think we have to wrap above SQL query with another select.

Still getting 

MACRO SQL ERROR - No column name was specified for column 1 of 'zyanuct3_3gu'. with adding AS [N]. 

Maybe I should test my code myself before sending it over....

Another try, wrapped the above query in another select statement to get rid of the strange xml-named-columns

${SQL: SELECT(SELECT M.FullName + ' (' + S.StatusName + ')' +', ' FROM ContainerMemberSnapshots AS M INNER JOIN StatusInfo AS S on M.[Status]=S.StatusId WHERE status != 0 AND status != 1 AND status != 9 AND status != 27 AND ContainerID = ${N=SwisEntity;M=ContainerID} FOR XML PATH('') ) AS N}

I've tried the last update to the code and it didn't work.  I'm trying to install the Orion SDK having problem getting admin access to me PC on this, thanks again for all you help with this, once we can get this figured out it would be a great blessing to me.  thanks again

I've seen that strange error before with the sql macros... and cannot recall what I did to resolve it, if it comes to me I'll reply here!

Ok, thanks

Hi There, thanks for the advice, but in using ${N=OrionGroup;M=GroupStatusRootCause}, this gives you things that are un-managed and etc., What I want is those things that are only in Warning and Critical, all the other stuff is distraction and is not the root cause.  I wish they had a function like this without (Unknown,up,unmanaged, and disable).  So I would like to get rid of this exceptions.