Custom SQL - All CPU Avg

Help
1

In the documentation, an advanced query to calculate all cpu avg instead a single core one is provided: https://docs.librenms.org/Alerting/Rules/#advanced

That query doesnt work now, but I dont have enough knowledge to build the new query.

This is the error:

[2020-05-15 07:07:31] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: SELECT *,AVG(processors.processor_usage) as cpu_avg FROM devices,processors WHERE (devices.device_id = 117 AND devices.device_id = processors.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 HAVING AVG(processors.processor_usage) > 95) (SQL: SELECT *,AVG(processors.processor_usage) as cpu_avg FROM devices,processors WHERE (devices.device_id = 117 AND devices.device_id = processors.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 HAVING AVG(processors.processor_usage) > 95)

This error comes from using strictmode in the sql queries: https://github.com/librenms/librenms/blob/2f86c66afacc33611ff79b5d35b15b1197104e43/LibreNMS/DB/Eloquent.php#L78-L87

How should I group that query to make it work?

2

I dont like to do that…but bumping a bit.

3

Either drop the * from the query or add them to a group by clause.

4

Where or by what should I group by? I tried adding it at end by several fields but all drop error

5

groupby must include every selected column.

6

Wait… Do you mean I must group by every column in devices table as Im selecting * from devices?

Something like:

SELECT *,AVG(processors.processor_usage) as cpu_avg FROM devices,processors WHERE (devices.device_id = 117 AND devices.device_id = processors.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 HAVING AVG(processors.processor_usage) > 95 GROUP BY device_id,device_hostname,device_ip…

???

7

yep, so it would be easier to select only the columns you need instead of *. (and less likely to break if the db structure changes)