SQL Query in Grafana

1

I have a table with 2 columns: TimeStamp and Description.
TimeStamp is DateTime and Description is nvarcahr(512)

Each time when production starts or stops, in this table are inserted values like:

TimeStamp                 Description
15.11.2019 22:50:00        Line 1 ON
15.11.2019 23:00:00        Line 1 OFF

So, in Grafana I would like to present only the data from the description column, but showing “Started/Stopped” instead of “Line 1 ON” and the color of background of the panel to change green/red depending of the status.

I have tried with this query

SELECT top 1 Description
FROM dbo.ProductionLog
CASE
WHEN Description=='Line 1 ON' then 1 
when Description=='Line 1 OFF' then 0 
ELSE ERROR 
END
order by ProductionLogID DESC

but no results.
Can someone help me please?Grafana1 Grafana2

2

Is an special SQL database? because it seems initially wrong.

3

What type of database are you using?

How are you inserting the data into the database, if it is a script can you use it to interpret and change the data being inserted into the database?

4

It is MsSQLExpress data base, not some special. I have software that record the delays in production. That software reads the data from OPC server that read the data from PLC. That software when read data it write it to MSSQLExpress. But it writes the data as i described in my question.

5

Sorry mate I probably can’t help much further, as I’m not familiar with any of that MS stuff.

I primarily use influxdb and python3 and sometimes a little node-red to interpret the data set received from a device, set my point values and variables and names to be written and send it to my database.

I would look at the software that writes the data to the database and see if you can change or manipulate the value being sent to be stored by the database at that level. It would also make your query(s) a lot easier to write and understand.

6

As I understood you want
“Line 1 On/Off” to be printed in Grafana, and you are using singlestat panel (you missed to specify)

Solution:
Check your query(I assume it’s working), You are mapping
’Line 1 on’ to 1 and
’Line 1 off’ to 0
using “CASE” and “WHEN”

then in Value Mapping of the panel(screenshot)
you are mapping 0 to “Stopped”
and 1 to “Running”, which you are getting on display.

so here replace text “Stopped” to “Line 1 is off”
“Running” to “Line 1 is on”

7

It is OK. Thanks you :slight_smile:

8

In Grafana has to be printed Started/Stoped not Line 1 ON/OFF.

9

But your question description does not state like that,

anyway as I assumed earlier your query is working(which is not I guess)
share the error of query(you will find it under space where you have written)

I think you may have a syntax error in query

Below query may work ( I have a working query like this so )
-the case will be before “from”
-no double equal-to

SELECT top 1 Description,
CASE
WHEN Description=‘Line 1 ON’ then 1
when Description=‘Line 1 OFF’ then 0
END as Description
FROM dbo.ProductionLog
order by ProductionLogID DESC

1 Like
10

Hi, sorry for my late respond,

i have tested your query, but i get NO VALUE.

Br

11

I guess your time range is out of records, it need to be such that you are getting last timestamp entry (if your database is not adding live entries), so play with time range at top right (as NO VALUE means no record I guess)

12

Hi there,

so your query is working.
Thanks you for your help.

1 Like