13 Replies Latest reply on Feb 28, 2012 8:17 AM by handbrake2

    Alerting Based on Node Groups rather than Custom Properties

    mazydizzle2K10

      Hi all,

      we want to do away with our custom properties as we have just come accross the grouping with dynamic SQL queries which we feel is a better way to group our nodes dynamicly and automaticly.

      However...

      In Alert Manager, I cannot seem to base an Alert Trigger condition on Node Groups. Is this possible?

      THanks

      Mario

        • Re: Alerting Based on Node Groups rather than Custom Properties
          netlogix

          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.

            • Re: Alerting Based on Node Groups rather than Custom Properties
              mazydizzle2K10

              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

                • Re: Alerting Based on Node Groups rather than Custom Properties
                  mazydizzle2K10

                  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

                    • Re: Alerting Based on Node Groups rather than Custom Properties
                      netlogix

                      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

                        • Re: Alerting Based on Node Groups rather than Custom Properties
                          mazydizzle2K10

                          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

                            • Re: Alerting Based on Node Groups rather than Custom Properties
                              mazydizzle2K10

                              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

                                • Re: Alerting Based on Node Groups rather than Custom Properties
                                  netlogix

                                  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.)

                                    • Re: Alerting Based on Node Groups rather than Custom Properties
                                      mazydizzle2K10

                                      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

                                        • Re: Alerting Based on Node Groups rather than Custom Properties
                                          mazydizzle2K10

                                          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')

                                          )

                                            • Re: Alerting Based on Node Groups rather than Custom Properties
                                              mazydizzle2K10

                                              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

                                              • Re: Alerting Based on Node Groups rather than Custom Properties
                                                mazydizzle2K10

                                                Ah,

                                                i've just realised i needed an extra ).

                                                got this all working now with a list. Excellent!

                                                Just need to figure out how to apply the alert to a parent group which would then apply to the child/sub-groups.

                                                Mario

                                                  • Re: Alerting Based on Node Groups rather than Custom Properties
                                                    netlogix

                                                    I don't know if this helps, but you can do more of a wild card thing i.e.:

                                                    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 like '% Switches' and not Containers.Name like '% User Switches'
                                                    )

                                                    The % is a wild card *.* would be %.% for a sql like command.

                                                      • Re: Alerting Based on Node Groups rather than Custom Properties
                                                        handbrake2

                                                        Hi guys,

                                                        I have 2 Custom Groups defined: "Main Campus Switches and Routers" and "CDRC Switches and Routers".

                                                        Thanks to another thread, I was able to generate an Alert when a member of either of these groups goes down. However I now need to generate alerts for the following conditions.

                                                        1. Someone administratively shuts down or brings up an interface on a group member.
                                                        2. The CPU utilization goes above 80% (trigger) or falls below 60% (reset) on a group member.
                                                        3. The memory utilization goes above  80% (trigger) or falls below 60% (reset)  on a group member.
                                                        Since this requires both Group and Node properties I assume that I need to use custom SQL queries. I am not very SQL literate. Can anyone assist me with the code that I need to generate these alerts?
                                                        Regards,
                                                        David.