Help with SQL query for alert rule

[Noct3]

Hello,

I am having trouble creating a custom alert. This is mostly due to my lack of SQL knowledge really.

Basically, I want to create an alert that triggers when a value is more than 80% of 2 combined values.
These values are in the application_metrics table and are names df_RBD-EC_avail and df_rbd_used. There 2 values combined give the total available space of the pool.

MariaDB [librenms]> select * FROM application_metrics;
+--------+------------------------------------+-----------------+-----------------+
| app_id | metric                             | value           | value_prev      |
+--------+------------------------------------+-----------------+-----------------+
|      3 | df_c_avail                         | 611228288139260 | 611228288139260 |
|      3 | df_c_objects                       | 257507844161540 | 257508722933760 |
|      3 | df_c_used                          | 353183724388350 | 353183009521660 |
|      3 | df_device_health_metrics_avail     |  52133420335104 |  52133671993344 |
|      3 | df_device_health_metrics_objects   |              44 |              41 |
|      3 | df_device_health_metrics_used      |       338827006 |       336683607 |
|      3 | df_rbd_avail                       |  52133420335104 |  52133671993344 |
|      3 | df_rbd_objects                     |              45 |              47 |
|      3 | df_rbd_used                        |       144364335 |       136079123 |
|      3 | df_RBD-EC_avail                    | 104266840670210 | 104267343986690 |
|      3 | df_RBD-EC_objects                  |        46965069 |        46965067 |
|      3 | df_RBD-EC_used                     | 349948280250140 | 349947543691030 |
|      3 | osd_osd.0_apply_ms                 |              12 |              40 |
|      3 | osd_osd.0_commit_ms                |              12 |              40 |
|      3 | osd_osd.1_apply_ms                 |               3 |               1 |
|      3 | osd_osd.1_commit_ms                |               3 |               1 |
...

The current value would be 454215120920350 (104266840670210 + 349948280250140).

I could build a query with a static value but I don’t want to have to change it every time the pool grows in size.

Here the current rule the I have:

Like I said, I could manually input the threshold value but I’d prefer it to be more dynamic and adjust with the pool size.

How can I make the alert trigger when application_metrics.value is less than ((df_RBD-EC_avail + df_RBD-EC_used) * 0.80)? I am sorry if this does not make much sense but I essentially was trying to represent 80% of these 2 values.

Thank you

[slalomsk8er]

In this case, I would use the mysql workbench or other SQL editor to figure out the query and use the Advanced tab to imput what I came up with.

[Noct3]

Thanks for the reply! I see, I didn’t know about MySQL Workbench. It appears that I can connect to a MySQL instance remotely so that would indeed help. I will give it a try.

Thanks!

[Noct3]

I just wanted to update the thread with the solution that I found following your suggestion to use MySQL Workbench.

This works for me. It alerts me when the available storage is less than 20% of the sum of both the df_RBD-EC_avail and df_RBD-EC_used values.

SELECT * FROM devices,applications,application_metrics 
WHERE devices.device_id = ? 
AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 
AND application_metrics.metric LIKE '%df_RBD-EC_avail%' 
AND application_metrics.value <= (SELECT ((SUM(value) * 20) / 100) 
FROM librenms.application_metrics 
WHERE metric = 'df_RBD-EC_used' || metric = 'df_RBD-EC_avail');

Thanks again!