I have two SQL select command that works in the mariadb cli. They both compare the in and out rates, and calculate the % difference between them. One checks they’re 20% apart and the other 40%.
I had to add a minimum rate to avoid false positives of kb
SELECT *,sys.format_bytes(ports.ifInOctets_rate * 8) as port_in_rate,sys.format_bytes(ports.ifOutOctets_rate *8) as port_out_rate, ABS(((CAST(ifInOctets_rate AS SIGNED) - CAST(ifOutOctets_rate AS SIGNED)) / ((ifInOctets_rate + ifOutOctets_rate) / 2)) * 100) AS percentage_difference FROM devices, ports WHERE (devices.device_id = ? AND devices.device_id= ports.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) AND ports.ifSpeed = 25000000000 AND (ifInOctets_rate > 10000000 && ifOutOctets_rate > 10000000) AND ABS(((CAST(ifInOctets_rate AS SIGNED) - CAST(ifOutOctets_rate AS SIGNED)) / ((ifInOctets_rate + ifOutOctets_rate) / 2)) * 100) > 40
I have no idea if it’s a bug or not but using the Advanced SQL is a headache. I turn on the advanced SQL switch and paste my SQL statement substituting the device_id with ?. I then try and use the “Import from” and “sql query”, not matter what I put in that box (and I do mean that), it says “Your query could not be parsed”. So I just selected a port up macro and toggled Yes. Is this a broken feature? I expected to put in my sql query and select my derived percentage_difference field.
The 20% ones used to fire and gave me all the data in the details, including the derived fields from my sql. Then it just randomly stopped alerting, even though the cli shows multiple rows. I have no idea why.
The 40% one seems to be firing, as I get alerts in the history ,but when I click on detail, it just shows “No Details found”. Again, I have no idea why.
Is there better documentation about the advanced sql? The docs are seriously lacking.
Any tips would be appreciated.
./validate.php
| Component | Version |
|---|---|
| LibreNMS | 23.10.0-9-g88b9744e0 (2023-10-30T17:13:28-04:00) |
| DB Schema | 2023_10_20_075853_cisco_asa_add_default_limits (268) |
| PHP | 8.1.2-1ubuntu2.14 |
| Python | 3.10.12 |
| Database | MariaDB 10.6.12-MariaDB-0ubuntu0.22.04.1 |
| RRDTool | 1.7.2 |
| SNMP | 5.9.1 |
| =========================================== |
[OK] Composer Version: 2.6.5
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database Schema is current
[OK] SQL Server meets minimum requirements
[OK] lower_case_table_names is enabled
[OK] MySQL engine is optimal
[OK] Database and column collations are correct
[OK] Database schema correct
[OK] MySQl and PHP time match
[OK] Active pollers found
[OK] Dispatcher Service not detected
[OK] Locks are functional
[OK] Python poller wrapper is polling
[OK] Redis is unavailable
[OK] rrd_dir is writable
[OK] rrdtool version ok