Hello. We are seemingly unable to evaluate multiple BGP peers in the same singular alert rule:
Here is the SQL format of the alert rule. I’m including screenshots as well:
SELECT * FROM devices,bgpPeers WHERE (devices.device_id = ? AND devices.device_id = bgpPeers.device_id) AND bgpPeers.bgpPeerDescr = “CUSTOM_BGP_PEER_1” AND bgpPeers.bgpPeerState = “established” AND bgpPeers.bgpPeerFsmEstablishedTime < 360 AND (bgpPeers.bgpPeerDescr = “CUSTOM_BGP_PEER_2” AND bgpPeers.bgpPeerState = “established” AND bgpPeers.bgpPeerFsmEstablishedTime < 360)
Adding each of these groups individually works fine,
But this does not work:
Perhaps this is by design, something that we are not understanding. But at this point we’ve exhausted every permutation of the rule, including creating Custom SQL queries that work properly when tested against the database itself, but fail when copied into the alert engine as SQL overrides.
ll.
This is pure SQL issue here. You cannot have, in the same select, 2 different values for BGP peer desc …
If the value is (true) for bgpPeerDesc=CUSTOM_BGP_PEER_1 in the 1st half, then it CANNOT be (true) for the 2nd, and because you ask “AND” the result will always be (false) at the end of the rule …
So, how would someone create an alert rule that triggers based on the state of more than one BGP peer ? My use of custom SQL is only forced by the seeming inability of the gui alert engine to assess more than one bgp peer. I’m admittedly not a database guy and don’t have much knowledge about SQL query syntax. Is there a solution that I’m not seeing or is this a limitation of the engine ?
I still don’t get what you want to do. But basically, if you can write the request in SQL, then you’ll be good to go in “custom sql”. Clearly in the GUI, you don’t have all the full power of SQL.
The SQL you added in the 1st post is wrong. You cannot have both bgpPeers.bgpPeerDescr = 'CUSTOM_BGP_PEER_1' and bgpPeers.bgpPeerDescr = 'CUSTOM_BGP_PEER_2' . Because that would mean 'CUSTOM_BGP_PEER_1' = 'CUSTOM_BGP_PEER_2 which is obviously wrong. It will never fire any result.