Bug: incorrect formation of SQL request when creating a alert rule

Help
1

When creating a rule like in the screenshot

screen1
screen1708×305 41.5 KB

SQL request is formed:

SELECT * FROM devices,bgpPeers_cbgp,bgpPeers WHERE (devices.device_id = ? AND devices.device_id = bgpPeers_cbgp.device_id AND devices.device_id = bgpPeers.device_id) AND bgpPeers.bgpPeerFsmEstablishedTime > 300 AND bgpPeers.bgpPeerState = "established" AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND bgpPeers_cbgp.AcceptedPrefixes_delta < -10

You can see that the “devices” table is joined with the “bgpPeers” table by the “device_id” field, and the “bgpPeers_cbgp” table is also joined with the “devices” table by the “devices_id” field, but must be joined with the “bgpPeers” table by the “device_id” field AND “bgpPeerIdentifier” field, or by the field “bgpPeer_id” (it is more logical, but the field “bgpPeer_id” not presented in the table “bgpPeers_cbgp”). It should look like this:

SELECT * FROM devices,bgpPeers_cbgp,bgpPeers WHERE (devices.device_id = ? AND devices.device_id = bgpPeers.device_id AND bgpPeers.device_id = bgpPeers_cbgp.device_id AND bgpPeers.bgpPeerIdentifier = bgpPeers_cbgp.bgpPeerIdentifier) AND bgpPeers.bgpPeerFsmEstablishedTime > 300 AND bgpPeers.bgpPeerState = "established" AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND bgpPeers_cbgp.AcceptedPrefixes_delta < -10

or (it is more logical, but the field “bgpPeer_id” not presented in the table “bgpPeers_cbgp”)

SELECT * FROM devices,bgpPeers_cbgp,bgpPeers WHERE (devices.device_id = ? AND devices.device_id = bgpPeers.device_id AND bgpPeers.bgpPeer_id = bgpPeers_cbgp.bgpPeer_id) AND bgpPeers.bgpPeerFsmEstablishedTime > 300 AND bgpPeers.bgpPeerState = "established" AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND bgpPeers_cbgp.AcceptedPrefixes_delta < -10

Version: 1.45-16-g01666e5 - Thu Nov 08 2018 00:04:45 GMT+0700
DB Schema #270

2

Updated:
In the “bgpPeers” and “bgpPeers_cbgp” tables there are 3 entries for a device with device_id = 658:

SELECT bgpPeer_id,device_id,bgpPeerIdentifier,bgpPeerState
FROM bgpPeers
WHERE device_id=685;
bgpPeer_id device_id bgpPeerIdentifier bgpPeerState
84 685 10.142.127.121 established
85 685 10.143.248.101 established
86 685 10.250.113.49 idle 
SELECT device_id,bgpPeerIdentifier,afi,safi,AcceptedPrefixes
FROM bgpPeers_cbgp
WHERE device_id=685;
device_id bgpPeerIdentifier afi safi AcceptedPrefixes
685 10.142.127.121 ipv4 unicast 115
685 10.143.248.101 ipv4 unicast 115
685 10.250.113.49 ipv4 unicast 0

If you run a query that is generated when setting up a rule (the list of columns to display is changed):

SELECT bgpPeers.bgpPeer_id,bgpPeers.device_id,bgpPeers.bgpPeerIdentifier,bgpPeers.bgpPeerState,bgpPeers_cbgp.afi,bgpPeers_cbgp.safi,bgpPeers_cbgp.AcceptedPrefixes
FROM devices,bgpPeers_cbgp,bgpPeers
WHERE (devices.device_id = 685 AND devices.device_id = bgpPeers_cbgp.device_id AND devices.device_id = bgpPeers.device_id) AND bgpPeers.bgpPeerFsmEstablishedTime > 300 AND bgpPeers.bgpPeerState = "established" AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND bgpPeers_cbgp.AcceptedPrefixes_delta < 55555;
bgpPeer_id device_id bgpPeerIdentifier bgpPeerState afi safi AcceptedPrefixes
84 685 10.142.127.121 established ipv4 unicast 115
85 685 10.143.248.101 established ipv4 unicast 115
84 685 10.142.127.121 established ipv4 unicast 115
85 685 10.143.248.101 established ipv4 unicast 115
84 685 10.142.127.121 established ipv4 unicast 0
85 685 10.143.248.101 established ipv4 unicast 0

it can be seen that the records from the “bgpPeers” and “bgpPeers_cbgp” tables were incorrectly joined, all peers are repeated three times, peer 10.142.127.121 has 115 and 0 AcceptedPrefixes at the same time.

The same query, but changed the set of columns and omitted filtering, but the conditions for joining tables are left:

SELECT bgpPeers.bgpPeer_id,bgpPeers.device_id,bgpPeers.bgpPeerIdentifier as bgpPeers__bgpPeerIdentifier,bgpPeers_cbgp.bgpPeerIdentifier as bgpPeers_cbgp__bgpPeerIdentifier,bgpPeers.bgpPeerState,bgpPeers_cbgp.afi,bgpPeers_cbgp.safi,bgpPeers_cbgp.AcceptedPrefixes
FROM devices,bgpPeers_cbgp,bgpPeers
WHERE (devices.device_id = 685 AND devices.device_id = bgpPeers_cbgp.device_id AND devices.device_id = bgpPeers.device_id);
bgpPeer_id device_id bgpPeers__bgpPeerIdentifier bgpPeers_cbgp__bgpPeerIdentifier bgpPeerState afi safi AcceptedPrefixes
84 685 10.142.127.121 10.142.127.121 established ipv4 unicast 115
85 685 10.143.248.101 10.142.127.121 established ipv4 unicast 115
86 685 10.250.113.49 10.142.127.121 idle ipv4 unicast 115
84 685 10.142.127.121 10.143.248.101 established ipv4 unicast 115
85 685 10.143.248.101 10.143.248.101 established ipv4 unicast 115
86 685 10.250.113.49 10.143.248.101 idle ipv4 unicast 115
84 685 10.142.127.121 10.250.113.49 established ipv4 unicast 0
85 685 10.143.248.101 10.250.113.49 established ipv4 unicast 0
86 685 10.250.113.49 10.250.113.49 idle ipv4 unicast 0

thus, the SQL queries generated for the rules are formed incorrectly and lead to erroneous triggers.