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 

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 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.    

I've added a Column name to the Select line and the end called 'COLN'  and the query works when I remove the statement of 'FOR XML PATH('')'. But the only returns only 1 component back.  When I add the "FOR XML PATH('')" statement I get the  MACRO SQL ERROR - No column name was specified for column 1 of '0tlouona_hdf'.  Error.  

Also in MSSMS the result look like this : 

So when in run this within SolarWinds it seems like it don't know what to do with the top line of the return, here my query . yes the XML Path flattens the return  to 1 return line of data. 

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