cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

Orion alerting will only pull the first cell from a SQL query

Jump to solution

I've got a request from one of my tech owners to alert when a node goes down, but also include information from the rest of the nodes in the cluster. I've created a test group containing 2 nodes labelled with Cluster_Number custom property.

Data that I want to see:

Caption           IP_Address          Cluster_Number           StatusDescription

Laptop-1         10.1.1.1                1 of 2                          Node status is Up.

Laptop-2         10.1.1.2                2 of 2                          Node status is Up.

I've created a SQL query I can run from SSMS that gives me the correct data:

select nodes.Caption, nodes.IP_Address, custom.Cluster_Number, nodes.StatusDescription

from dbo.NodesData as nodes

join dbo.NodesCustomProperties as custom on nodes.NodeID = custom.NodeID

left outer join dbo.ContainerMemberSnapshots as members on nodes.caption = members.name

left outer join dbo.Containers as containers on containers.ContainerID = members.ContainerID

where containers.Name like 'Test%'

When I edit the alert to include this query, I only get "Laptop-1." I've broken the query into pieces to pull all columns, which I join together in the alert using " -- " to make the data more readable:

${SQL: select nodes.Caption from dbo.NodesData as nodes

left outer join dbo.ContainerMemberSnapshots as members on nodes.caption = members.name

left outer join dbo.Containers as containers on containers.ContainerID = members.ContainerID

where containers.Name like 'Test%'}

${SQL: select nodes.IP_Address from dbo.NodesData as nodes

left outer join dbo.ContainerMemberSnapshots as members on nodes.caption = members.name

left outer join dbo.Containers as containers on containers.ContainerID = members.ContainerID

where containers.Name like 'Test%'}

${SQL: select custom.Cluster_Number from dbo.NodesCustomProperties as custom

join dbo.NodesData as nodes on nodes.NodeID = custom.NodeID

left outer join dbo.ContainerMemberSnapshots as members on nodes.caption = members.name

left outer join dbo.Containers as containers on containers.ContainerID = members.ContainerID

where containers.Name like 'Test%'}

${SQL: select nodes.StatusDescription from dbo.NodesData as nodes

left outer join dbo.ContainerMemberSnapshots as members on nodes.caption = members.name

left outer join dbo.Containers as containers on containers.ContainerID = members.ContainerID

where containers.Name like 'Test%'}

When the alert fires, I see this:
Laptop-1 -- 10.1.1.1 -- 1 of 2 -- Node status is Down.

I need to see the other group member as well. While this particular group only has 2 members, I need to be able to deploy this alert in a variety of environments where clusters may have several members. How can I get Orion to display more than just the first line from the query?

1 Solution
Level 17

I found a couple of typos in your last query. It was missing the 2 letters in red.

SELECT TOP 25 NodesData.Caption + ' | ' as 'data()'

    FROM [dbo].[NodesData]

    left outer join ContainerMemberSnapshots on NodesData.caption = ContainerMemberSnapshots.name

    left outer join Containers on Containers.ContainerID = ContainerMemberSnapshots.ContainerID

    WHERE Containers.Name like 'Test%'

FOR XML PATH ('')

For your original SQL query, the query can be modified as below

SELECT nodes.Caption + '    ' + nodes.IP_Address + '     ' + custom.Cluster_Number +  '     ' + nodes.StatusDescription + CHAR(13) + CHAR(10) AS 'data()'

FROM dbo.NodesData AS nodes

JOIN dbo.NodesCustomProperties as custom ON nodes.NodeID = custom.NodeID

LEFT OUTER JOIN dbo.ContainerMemberSnapshots AS members ON nodes.caption = members.name

LEFT OUTER JOIN dbo.Containers AS containers ON containers.ContainerID = members.ContainerID

WHERE containers.Name LIKE 'Test%' FOR XML PATH('')

also, node that the macros such as ${NodeID} would need to be determined specific to the alert. The correct syntax depends on the alert object, and you can determine the correct syntax by using the 'Insert Variable'  button. Please see my response here on how to arrive at the variable name

Building an interfaces Alert with Nodes Custom Properties

View solution in original post

4 Replies
Level 17
0 Kudos
Level 8

I tried your suggestion, but didn't get the results I was looking for.

First, I tried the actual query you listed:

     ${SQL:SELECT TOP 25 Message_UNICODE + ' | ' as 'data()' FROM [dbo].[NodesData] WHERE NodeID=${NodeID} FOR XML PATH ('')}

which unsurprisingly didn't work correctly, as it was pulling data customized for the other user:

     MACRO SQL ERROR - Invalid column name 'Message_UNICODE'.

Next, I dropped the formatting and edited the query to hopefully pull the top 25 captions:

     ${SQL:SELECT TOP 25 Caption FROM [dbo].[NodesData] WHERE NodeID=${NodeID}}

and only got one result, which I chaled up to the fact the query was referencing a single node:

     Laptop-1

Next, I edited the first part of my original query to include "top 10 *":

     ${SQL: select top 10 * from dbo.NodesData as nodes

     left outer join dbo.ContainerMemberSnapshots as members on nodes.caption = members.name

     left outer join dbo.Containers as containers on containers.ContainerID = members.ContainerID

     where containers.Name like 'Test%'}

and only got one result, which was the first item in the table, the NodeID:

     1131

Finally, I tried using not using symbolic names for the tables:

     ${SQL:SELECT TOP 25 NodesData.Caption + ' | ' as 'data()'

     FROM [dbo].[NodesData]

     left outer join dbo.ContainerMemberSnapshots on NodesData.caption = ContainerMemberSnapshot.name

     left outer join dbo.Containers on Containers.ContainerID = ContainerMemberSnapshot.ContainerID

     WHERE Containers.Name like 'Test%' FOR XML PATH ('')}

and got a different macro SQL error:

     MACRO SQL ERROR - The multi-part identifier "ContainerMemberSnapshot.name" could not be bound.

     The multi-part identifier "ContainerMemberSnapshot.ContainerID" could not be bound.

I wasn't able to resolve the table.column name issue in the last example. Perhaps querying "TOP 10" only works if you're pointing Orion to a single node ID?

0 Kudos
Level 17

I found a couple of typos in your last query. It was missing the 2 letters in red.

SELECT TOP 25 NodesData.Caption + ' | ' as 'data()'

    FROM [dbo].[NodesData]

    left outer join ContainerMemberSnapshots on NodesData.caption = ContainerMemberSnapshots.name

    left outer join Containers on Containers.ContainerID = ContainerMemberSnapshots.ContainerID

    WHERE Containers.Name like 'Test%'

FOR XML PATH ('')

For your original SQL query, the query can be modified as below

SELECT nodes.Caption + '    ' + nodes.IP_Address + '     ' + custom.Cluster_Number +  '     ' + nodes.StatusDescription + CHAR(13) + CHAR(10) AS 'data()'

FROM dbo.NodesData AS nodes

JOIN dbo.NodesCustomProperties as custom ON nodes.NodeID = custom.NodeID

LEFT OUTER JOIN dbo.ContainerMemberSnapshots AS members ON nodes.caption = members.name

LEFT OUTER JOIN dbo.Containers AS containers ON containers.ContainerID = members.ContainerID

WHERE containers.Name LIKE 'Test%' FOR XML PATH('')

also, node that the macros such as ${NodeID} would need to be determined specific to the alert. The correct syntax depends on the alert object, and you can determine the correct syntax by using the 'Insert Variable'  button. Please see my response here on how to arrive at the variable name

Building an interfaces Alert with Nodes Custom Properties

View solution in original post

Level 8

Thanks, that worked perfectly!

0 Kudos