Time line chart showing occurrences per given range

Grafana Dashboards
1

Grafana version 9.4.3

A timeline chart on my dashboard that displays the number of occurrences of an event over a selected time range in blocks of a selected time interval

I have some live data via a mysql database (via PLC) that displays system performance of a factory floor process. The data includes a TimeDate field for each event and 8 additional columns of information.
By using Variables I can filter this data in table format to show the correct information, but I would like to display this as a timeline, highlighting the number of occurrences per hr (for example)

Can someone please advise how to display this data ? This is what I have so far…

SELECT
  $__timeGroup(TimeDate,'1h') as time_sec,
  COUNT(Fault) as value,
  Program as metric
FROM AGVfault
WHERE $__timeFilter(TimeDate)
GROUP BY 1, Program
ORDER BY 1

image
image936×728 57.6 KB

2

Do you get anything when you change the visualization to a Time Series graph?

If you get nothing, my initial guess is that a Transformation will fix the issue.

3

Hi, sorry I forgot to state that the data table is without the timegroup function. I cannot seem to get it to work without errors.

db query error: mssql: Each GROUP BY expression must contain at least one column that is not an outer reference.

The above is the current error. this is not a function I have used before, so I guess i am just missing something. Thanks for the support.

4

I am still not sure what I am doing with this, but the results below seem a little closer? the query seems to fall over when I add the 1, to the group line?

SELECT $__timeGroup(TimeDate,'1h') as time_sec, COUNT(Fault) as value

FROM AGVfault

WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Fault in ($Fault)

GROUP BY TimeDate

ORDER BY 1

image
image1511×461 22.7 KB

The image above shows the data, but it looks as though each event is still a single entry and not grouped by hour.

Thanks for any support that can be given.

5

Please provide DDL and DML for

AGVfault

CREATE TABLE AGVfault(
..
..
..
)
6

Hi, thanks for the reply. I don’t think this what you are after, but I’m not sure how to supply the correct data?
I am still very new to Grafana so will require support to provide information.

none filtered query below, return aprox 800 rows per hour.

SELECT * FROM AGV.dbo.AGVfault 
WHERE $__timeFilter(TimeDate) AND Program <> 0

image
image1786×274 34.6 KB

Data headers (type) as follows

TimeDate (Date Stamp)
AGV (Integer)
Program (Integer)
Step (Integer)
Junction (Integer)
Zone (Integer)
Station (Integer)
BattVoltage (Integer)
Fault (String)

I am looking to count the number of rows generated per hour, so resulting table would look like the excel chart below.

image
image1180×452 13 KB

Thanks again

7

Hi.

was anyone able to help with this? I am still having issues.

8

Hi

I am still stuck on this problem. unsing the internet and trying various methods the below get me the closest to a working solution, but its still not correct.
I now have a table displaying the correct number of faults per hour, filtered by my variables, in a table form, but I cant seem to mange to add a second column, to indicate the time range split.

SELECT COUNT(Fault) as value
FROM AGVfault
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND Fault in ($Fault) and AGV in ($AGV)
GROUP BY $__timeGroup(TimeDate,1h,0)

Any help would be much apreciated, also if I am on the wrong track, please explain how I should be approaching this problem.

Thanks

image
image1507×759 43.7 KB

9

To close this thread, please find the solution to my question below.
Thanks for the continued support :slight_smile:

SELECT $__timeGroup(TimeDate,6h,0) as time, COUNT(Fault) as value
FROM AGVfault
WHERE $__timeFilter(TimeDate) AND Program <> 0 AND Program in ($Program) AND Fault in ($Fault) and AGV in ($AGV)
GROUP BY $__timeGroup(TimeDate,6h,0)
ORDER BY time ASC

image
image1822×354 98.5 KB

I have some follow-up questions, but I have started a new thread.