Alert when Interface within a Specific Group is Down

Hi!

I need to generate an alert when an interface belonging to a node in a specific group goes down.

I've worked out the following so far, which shows which group that interface is in.

NOTE: I don't have interfaces added to groups. I am generating this report to display all the interfaces belonging to the Node which is in the group.

This is what I've accomplished so far:

SELECT Intf.InterfaceID,intf.caption as InterfaceCap,co.nameFROM [dbo].[Containers] coleft outer join ContainerMemberSnapshots sn on co.ContainerID = sn.ContainerIDleft outer join [dbo].[Nodes] Nod on sn.EntityID = Nod.NodeIDleft outer join [Interfaces] Intf on Nod.NodeID = Intf.NodeIDleft outer join [StatusInfo] St on Intf.Operstatus = St.StatusIDWHERE Intf.OperStatus <> 1and co.name in ('GRP001','GRP002')ORDER by co.ContainerID

I am only interested in the interfaces that are in the GRP001 and GRP002 groups. Also, I am only interested in interface status which is not up.

This works well:

Interface Group Membership.png

Now here's the issue I am facing. I need to create an alert if an interface in groups GRP001 and GRP002 is not up.

Pasting the query and testing it in Advanced Alert Manager doesn't work as it should because Advanced Alert Manager is not respecting the filter on group.

I presume that is because there's a predefined query that the SQL Alert is executing:

Interface Group Membership SQL Alert.png

I can confirm this by looking at the TriggerQuery in AlertDefinitionin:

Interface Group Membership SQL Alert TriggerQuery.png

So the question now is, can I create a Custom SQL Alert without there being a predefined query attached to it?

If not, could I safely modify the query in the TriggerQuery column for this alert to only include my own SQL query?

Would this break anything or be reverted at some point (db maintenance, running configuration wizard, updates etc)?

Find more posts tagged with

Accepted answers

JiriPsota

SELECT Interfaces.InterfaceID AS NetObjectID, Interfaces.FullName as Name

FROM Interfaces

------------------------------------------------------------------------------------------------------------------

JOIN Nodes nod ON nod.NodeID=Interfaces.NodeID

JOIN ContainerMemberSnapshots sn ON sn.EntityID=nod.NodeID

JOIN Containers co ON co.ContainerID=sn.ContainerID

JOIN StatusInfo st ON Interfaces.OperStatus=st.StatusId

WHERE Interfaces.OperStatus<>1

AND co.name IN ('GRP001','GRP002')

ORDER BY co.ContainerID

This should work, but when the same interface (or node) is a member of more groups, it will trigger only once.

All comments

Deltona

BUMP!

njoylif

did you try creating (in dev environment) a new SQL alert, then customizing the query through SQL? does it prevent that?

I know that is not ideal, but if this is pressing, I'd try that and/or open a ticket.

I do suspect this won't work b/c of though trigger query design though.

Deltona

I regretfully don't have the resources to perform this type of test

Maybe JiriPsota could help?

bluefunelemental

I just got done a custom SQL alert that was a bear. the scary kind.

You need to delete your select statement so it joins in with the SQL alarm select- meaning you need a rewrite to join your containers table to the interfaces table they select from. Your entry should just be joins and then the where statement.

And I would do the joins as left inner joins assuming you only need data that does match where the interface is down vs rows that don't match the interfaces.

edit- my sql developer corrected me that your left outers are faster then what I meant to type as just inner joins.