Alerting Based on Node Groups rather than Custom Properties

Right now there isn't a way using the standard alerts pages, but you can use a custom SQL alert and use this:

(
Caption in (Select ContainerMemberSnapshots.Name
             from ContainerMemberSnapshots
                inner join Containers on
                Containers.ContainerID = ContainerMemberSnapshots.ContainerID
             where Containers.Name = '<*****PUT YOUR GROUP NAME HERE*****>')
) and (
Not Caption in (Select ContainerMemberSnapshots.Name
             from ContainerMemberSnapshots
                inner join Containers on
                Containers.ContainerID = ContainerMemberSnapshots.ContainerID
             where Containers.Name = '<*****GROUP NAME TO IGNORE HERE*****>')
)

@Solarwinds - I don't know if this has made it to your features to add list, but add it, please.

Hi Netlogix,

thanks a lot for that script...

Can you just explain what the second condition is for?

Just to give you am example of what I am trying to achieve.

We will have 3 dynamic groups

-Access Switches

-Distribution Switches

-Core Switches

I would like to trigger interface down alerts on Core and Distribution switches, but not access switches.

How would I modify your code to achieve something like that?

thanks again

Mario

OK...

this is what my WHERE statement looks like now...

where (interfaces.Status = 0) AND (

Caption in (Select ContainerMemberSnapshots.Name

from ContainerMemberSnapshots

inner join Containers on

Containers.ContainerID = ContainerMemberSnapshots.ContainerID

where Containers.Name = 'XXX Distribution Switches')

)

It is not working.

Have I got your code in the right place?

When creating a SQL query in Alert Manager, it does not let me change the SELECT statement that it automaticaly populates when you select the type of resource that you want to base your alert on.

I have not got a SELECT statement that includes the interfaces.Status field. Should I have?

Thanks

Mario

Is the Advanced SQL a Interface alert?  And the Group a Node Group?

If so, try:

where (interfaces.Status = 0) AND (
  Node.Caption in (Select ContainerMemberSnapshots.Name
    from ContainerMemberSnapshots
      inner join Containers on
         Containers.ContainerID = ContainerMemberSnapshots.ContainerID
  where Containers.Name = 'XXX Distribution Switches')
)

The caption it was getting was probably the Interface caption not the node's caption.  I am not positive if it's node.caption or nodes.caption

Hi netlogix,

yes this is an Advanced SQL alert in Interfaces and the trigger condition must be...

Interface = down

Node Group = Distribution Switches

I have tried to validate the SQL in your post and it fails advising that Node.caption and nodes.caption could not be found.

I'll try searching the SW tables to see if i can figure out what the right table field is.

Mario

Hi,

Sorry,

the error message is advising that the Multi-part identifier "Nodes.Caption" could not be Bound. Not Found. I mis-read the message.

Does that mean anything to you?

Thanks

Mario

Try this:

  inner join Nodes on Interfaces.NodeID = Nodes.nodeID
where (interfaces.Status = 0) AND (
  Nodes.Caption in (Select ContainerMemberSnapshots.Name
    from ContainerMemberSnapshots
      inner join Containers on
         Containers.ContainerID = ContainerMemberSnapshots.ContainerID
  where Containers.Name = 'XXX Distribution Switches')
)

(I didn't realized that it didn't automatically join the nodes table.)

Mate, that worked a treat!

only thing I had to change was the interfaces.status = 2 rather than 0. I looked in the table and found that interfaces that are down are given a value of 2.

Do you think that this will work if it is applied to a group that has subgroups in it? Or do you think I have to list the groups in the where containers.name ='group name' bit??

How would I specify a list?

thanks very much for your help on this!

Mario

Right, i just tried to turn the where = command to a where IN command so that I can specify a list of group names and I get a SQL syntax error when validating the syntax.

I am new to SQL so not sure if I have done the where clause right... Can someone check this over and give me any pointers?

inner join Nodes on Interfaces.NodeID = Nodes.nodeID

where (interfaces.Status = 2) AND (

Nodes.Caption in (Select ContainerMemberSnapshots.Name

from ContainerMemberSnapshots

inner join Containers on

Containers.ContainerID = ContainerMemberSnapshots.ContainerID

where Containers.Name IN ('XXX Distribution Switches', 'XXX Core Switches')

)

Hi,

I've just tried to use a Parent Group to base the alert on hoping that it would include nodes in the subgroups however it has not worked.

Looks like i will have to list every sub-group which is a shame, unless you know of a way to make this work with parent groups and subgroups?

Mario