1 Reply Latest reply on Mar 23, 2015 7:37 PM by RichardLetts

    Alert Based on Average Status of Group

    patrickhw

      Hi there, I'm looking for help on creating an alert.

       

      I'd like to be notified based on the average status of a group. For example, if 10 nodes out of 20 are down I'd like an alert sent. I'm not sure if this is possible but hopefully someone has some tips, thanks!

        • Re: Alert Based on Average Status of Group
          RichardLetts

          Hard.

           

          knowledge used: container membership is materialized periodically, and some nifty SQL to summarize the number of up and down nodes, and noting that when more than of the nodes are down less than half the nodes are up...

          UNTESTED <-- I have no nodes down right now...

           

          use a custom SQL query on the group object something like this:

          where groupid in (

          select ContainerID from ( SELECT c1.containerid,count(n1.nodeid) UpNode,count(N2.Nodeid) downNode

          FROM ContainerMemberSnapshots C1

          full outer join Nodes N1 on C1.EntityID=N1.Nodeid and N1.Status=1

          full outer join Nodes N2 on C1.EntityID=N2.Nodeid and N2.Status=2

          where C1.EntityType='Orion.Nodes'

          group by c1.containerid

          having count (n1.Nodeid) >=0 and count(N2.Nodeid) >=0 and (count (n2.Nodeid) >=count(N1.Nodeid))

          ) X

          )

           

          and the reset query is

          where groupid not in (

          select ContainerID from ( SELECT c1.containerid,count(n1.nodeid) UpNode,count(N2.Nodeid) downNode

          FROM ContainerMemberSnapshots C1

          full outer join Nodes N1 on C1.EntityID=N1.Nodeid and N1.Status=1

          full outer join Nodes N2 on C1.EntityID=N2.Nodeid and N2.Status=2

          where C1.EntityType='Orion.Nodes'

          group by c1.containerid

          having count (n1.Nodeid) >=0 and count(N2.Nodeid) >=0 and (count (n2.Nodeid) >=count(N1.Nodeid))

          ) X

          )

           

          Will generate an alert if more nodes are down than up; I would not run this very often -- as you should have 10 node down alerts