Advanced Alert SWQL Query Question

I am creating an alert with a custom SWQL query variable in it and I cannot get the actual data to show up. Here is my SWQL variable:

${N=SWQL;M=SELECT I.NodeID, I.InterfaceID, I.Name, N.NodeID, ICP.ifCustName, ICP.ifCustID
    FROM Orion.NPM.Interfaces AS I
    INNER JOIN Orion.Nodes AS N 
    ON I.NodeID = N.NodeID
    INNER JOIN Orion.NPM.InterfacesCustomProperties AS ICP
        ON I.InterfaceID = ICP.InterfaceID
WHERE I.NodeID = ${N=SwisEntity;M=NodeID} AND ICP.ifCustID > '1'}

This is supposed to be grabbing all interfaces on the defined NodeID that have a customer ID in it and display the 'ifCustId' and 'ifCustName'. This works fine in both SWQL Studio and SQL (when I convert it to SQL), but in the Advanced Alerts, the only output I get is the actual Node ID, not the table of useful data I am looking for. Can variables be called from within an SWQL query in the advanced alert? A cursory search of previous posts indicates this shouldn't be a problem, but idk. Is there an issue with how my query is set up? This is a Node alert, by the way, so I am not sure if that makes a difference.

Find more posts tagged with

custom swql alerts

Accepted answers

All comments

mesverrum

The SQL/SWQL variables in alert messages cannot display an entire table, they always only show the first column of the first row. There are tricks people use such as concatenating values into a single cell or using the for xml path command in SQL as discussed in this thread and others, SQL variable definition to return multiple rows

bleearg13

Thanks. I saw that posting, but my eyes started to glaze over because none of that SQL code is explained. I literally just learned how to do an inner join last week.

bleearg13

Since I have no idea what I am doing, this is what I came up with:

SELECT cast(cast([N.NodeID]as varchar(max)) + '' + '--' as XML),
cast(cast([I.NodeID]as varchar(max)) + '' + '--' as XML),
cast(cast([I.InterfaceName]as varchar(max)) + '' + '--' as XML),
cast(cast([I.ifCustID]as varchar(max)) + '' + CHAR(10) + CHAR(13) as XML)
FROM Interfaces AS I
INNER JOIN NodesData AS N
ON I.NodeID = N.NodeID
WHERE I.NodeID = 22 AND I.ifCustID > '1' FOR XML PATH('')

Unfortunately, all this does is error out and tell me 'Invalid column name'. I sort of understand what the CAST function does, but I have no clue what all the + '' and + '--' does. I assume the output shows whitespace and double dashes and these are to be interpreted as XML? I also don't know what the CHAR(10) and CHAR(13) are supposed to do or if they are even needed in my case. That being said, I am not sure any of those things are my problem. I'm also not sure I should be using CAST on the columns that are integers in the first place.

Have I mentioned that I don't know what I'm doing? I tried looking through several of the posts that I see now are trying to do the same thing and they all reference back to the one you posted, but without any other helpful information, such as what the OP ended up with as a result.

mesverrum

The idea is that all the +'s are there to concat strings together and the dashes and char(10) + char(13) (line breaks and carriage returns) are just there to format the data into something halfway readable. Instead of presenting it as a table with multiple cells it is smashing it all together as an xml doc in a single cell that in the end is supposed to get around the developers intention of limiting that feature to not pull back entire tables. For someone new to SQL it is a bit dense and I'll admit that I have only spent the time to make it work once and do not like reading that syntax at all, despite doing SQL/SWQL every day.

For things like this I normally just add a resource to the interface page and include a link so people can pull it up if they need that additional info. I've not been dedicated enough to fuss with the SQL to include tables in my messages