Hi guys , im trying to create a rule to detect traffic events…
When working under normal situation , traffic follows sinusoidal pattern.
In order to detect traffic events I will compare previus and current traffic value from database:
This query (for outgoing traffic) Im trying:
SELECT * FROM devices,ports WHERE (devices.device_id = 47 AND devices.device_id = ports.device_id) AND (SELECT IF(ports.ifOutOctets_delta> 0, (ports.ifOutOctets/ports.ifOutOctets_prev), 0)) >= X ;
So by analizing the relation between prevous and current octects I can check wheter there is a traffic event.
Problem is that query is not working as expected … If I use X= 1.5 for instance , I have 0 results , even when I have some positive results results for that condition.
Take a look:
MariaDB [librenms]> select ifOutOctets_prev,ifOutOctets, ifOutOctets/ifOutOctets_prev AS "calc" from ports order by calc desc limit 5; +------------------+-------------+---------+ | ifOutOctets_prev | ifOutOctets | calc | +------------------+-------------+---------+ | 12717 | 459035 | 36.0962 | | 10086 | 181833 | 18.0283 | | 2254719422 | 3082853861 | 1.3673 | | 437901093 | 557812705 | 1.2738 | | 145216 | 156946 | 1.0808 | +------------------+-------------+---------+
I know this is perhaps related to sql languaje , but would also share this concept to check if there is something already on going with this subject.
After fixing this , I would need also to adapt it to detect when traffic goes down.
Then create both macros in order to use them.
Regards.
Leandro.