Get all "Group Members" in email alert

dhardy

I want to be able to list all nodes and additional groups affected when an upstream group goes down.

Example.

A - B - C

Where A and B are groups (sites) and C is just a node.

In the email for Group a being "unreachable", I'd like for it to include

Nodes and Sites affected

B

C

 

I figured out that I can add an additional column to the ContainerMemberSnapshots table called MemberID

In this case I give B =1 and C = 2 ... where every other group dependency starts at one and increments as high as it needs to for the number of members.

In the email alert I added:

${SQL:Select Name FROM ContainerMemberSnapshots WHERE MemberID = '1'}

${SQL:Select Name FROM ContainerMemberSnapshots WHERE MemberID = '2'}

${SQL:Select Name FROM ContainerMemberSnapshots WHERE MemberID = '3'}

${SQL:Select Name FROM ContainerMemberSnapshots WHERE MemberID = '4'}

Where I'd just have maybe up to 20 or so if they exist.

 

Works great.. only problem is if there are no results returned as the MemberID gets higher I get:

${SQL:Select Name FROM ContainerMemberSnapshots WHERE MemberID = '3'}

${SQL:Select Name FROM ContainerMemberSnapshots WHERE MemberID = '4'}

This solution would work great if not for these 2 lines showing when no data is returned.

Am I just doing this the hard way? is there already a solution for this?