Sudden interface traffic increment / decrement alert rule detection

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.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.