Error in CPU Average Alert

Help
1

Hello

I have had an alert configured with a custom sql query to monitor the cpu that was working correctly:

SELECT *,AVG(processors.processor_usage) as cpu_avg FROM devices,processors WHERE (devices.device_id = ? AND devices.device_id = processors.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 HAVING AVG(processors.processor_usage) > 95

Recently, this alert has started to fail:
Error in alert rule Lucia - WARNING - Average CPU Usage > 80% for 10 min (75): 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 (Connection: mysql, SQL: SELECT *,AVG(processors.processor_usage) as cpu_avg FROM devices,processors WHERE (devices.device_id = 315 AND devices.device_id = processors.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 HAVING AVG(processors.processor_usage) > 80)

How can I solve that? Thanks

2
3

We are seeing this error now as well.

4

I can run SELECT *,AVG(processors.processor_usage) as cpu_avg FROM devices,processors WHERE (devices.device_id = ? AND devices.device_id = processors.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 HAVING AVG(processors.processor_usage) > 95 with no issues

5

@kurtwarner @laf
Laravel now includes stricter SQL. So running it in your favorite SQL client won’t show the error, unless you configure it to be as strict :
set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';

The last setting is the one that kills this SELECT I would say.

1 Like
6

I have tried a few different queries and not been able to get anything to work. Anyone else had any luck???

7

What have you tried?

8

I have tried adding different Group By commands at the end. With no success.

9

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

This works but doesn’t give you much information back for using in the template, you can build on this though.

10

Havent had a lot of time to look at this, but still getting this error

Error in alert rule Processor usage over 80% (20): SQLSTATE[HY093]: Invalid parameter number (Connection: mysql, SQL: SELECT devices.device_id,AVG(processors.processor_usage) as cpu_avg FROM devices,processors WHERE (devices.device_id=1 AND devices.device_id = processors.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 GROUP BY processors.processor_usage,devices.device_id HAVING processors.processor_usage > 95;)

11

The device_id=1 needs to be device_id=? when you put it in the advanced sql box