Error: Input must be a series

Grafana PostgreSQL
, ,
1
  • What Grafana version and what operating system are you using?

v9.4.7

  • What are you trying to achieve?

Making a an alert of the machines on the industrial field that has the duty_cycle > 80

  • How are you trying to achieve it?

I’m using a postgresql database with a table named ebbco_digital. On this table a have several different columns. The columns that I need are timestmap, machine_name, dutycycle. I’m trying to use this query:

SELECT duty_cycle, “timestamp”, machine_name FROM ebbco_digital WHERE $__timeFilter(timestamp) and duty_cycle is not null

After I’m trying to reduce it getting just the last value for each machine, and after trying to compare the duty_cycle value to make the alert

  • What happened?

I cannot use a query that return any column that is non-numeric such as machine_name so I get this error:

Error

Failed to evaluate queries and expressions: input data must be a wide series but got type long (input refid)

  • What did you expect to happen?

I expect to have a list like this

timestamp machine_name dutycyle
xxxx test_machine 80

To resume my query works without the machine name, but I would like to have the machine name to know in which machine I’m having the problem I also would like to send an email using the contact points and on the email I would like to send the machine name and the alert.

1 Like
2

Hi, did you find a solution? I have the same problem

SELECT
  hosts.computer_name as computer,
  last_audit_on::timestamp as time,
  (replace(replace(replace(hostpackagesstatus.last_audit_output, 'ecl-checkBatteryLife', ''),'Auditing:',''),'Auditing',''))::json->'returnReason' as result
FROM
  hostpackagesstatus
  LEFT JOIN hosts on hosts.uuid = hostpackagesstatus.host_id
WHERE hostpackagesstatus.last_audit_output ILIKE '%%NOK%%' AND hostpackagesstatus.package = 'ecl-checkBatteryLife'
order by time

Result in postgresql but nothing on grafana

[{"result":84.65,"computer":"22-0499-001","time":"2023-06-02T12:14:16.534134"},{"result":83.85,"computer":"22-0665-001","time":"2023-06-02T12:13:32.322480"},{"result":82.05,"computer":"22-0665-004","time":"2023-06-02T14:01:35.425184"},{"result":81.62,"computer":"22-0665-002","time":"2023-06-02T12:14:13.175704"},{"result":81.06,"computer":"22-0665-003","time":"2023-06-02T12:14:43.261377"},{"result":78.25,"computer":"19-E0891-020","time":"2023-06-06T07:10:37.213763"},{"result":72.87,"computer":"19-2313-003","time":"2023-06-02T11:49:52.295869"},{"result":57.8,"computer":"19-3229-001","time":"2023-06-05T07:19:01.668054"},{"result":51.63,"computer":"20-1281-002","time":"2023-06-02T14:01:21.401796"}]
1 Like
3

same problem here, no solution yet?

4

yes, it was two problems in my request :

  • a \n in my value corrected by the regexp
  • convert my value to decimal with ::decimal
SELECT
  hosts.computer_name as computer,
  (regexp_replace((replace(replace(replace(hostpackagesstatus.last_audit_output, 'ecl-checkBatteryLife', ''),'Auditing:',''),'Auditing','')), e'[\\n\\r]+', ' ', 'g' )::json->>'returnReason')::decimal as result
FROM
  hostpackagesstatus
  LEFT JOIN hosts on hosts.uuid = hostpackagesstatus.host_id
WHERE hostpackagesstatus.last_audit_output ILIKE '%%NOK%%' AND hostpackagesstatus.package = 'ecl-checkBatteryLife'