device_id | bucket | status
--------------+---------------------+--------
1 | 1970-01-01 01:22:30 | ACTIVE
1 | 1970-01-01 01:22:25 | ACTIVE
1 | 1970-01-01 01:22:45 | ACTIVE
1 | 1970-01-01 01:22:40 | ACTIVE
2 | 1970-01-01 01:22:35 | ACTIVE
2 | 1970-01-01 01:22:05 | ACTIVE
2 | 1970-01-01 01:22:20 | ACTIVE
2 | 1970-01-01 01:22:00 | ACTIVE
1 | 1970-01-01 01:22:15 | ACTIVE
1 | 1970-01-01 01:22:10 | ACTIVE
I think the state timeline is expecting data like this:
bucket | device1 | device2
-------------------------------+----------+--------+------
1970-01-01 01:22:30 | ACTIVE| ACTIVE
1970-01-01 01:22:25 | ACTIVE| ACTIVE
1970-01-01 01:22:45 | ACTIVE| ACTIVE
1970-01-01 01:22:40 | ACTIVE| ACTIVE
1970-01-01 01:22:35 | ACTIVE| ACTIVE
1970-01-01 01:22:05 | ACTIVE| ACTIVE
1970-01-01 01:22:20 | ACTIVE| ACTIVE
1970-01-01 01:22:00 | ACTIVE| ACTIVE
1970-01-01 01:22:15 | ACTIVE| ACTIVE
1970-01-01 01:22:10 | ACTIVE| ACTIVE
But I’m using Postgres, and to do a pivot table like this, I have to use crosstab() and hardcode the output columns. Can I use a data transformation do to this please?
Can you copy/paste the configuration(s) that you are having problems with?
Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
Unfortunately I’ve got an unknown number of devices at run time, so I can’t use multiple queries - this normally works by specifying the metric column in “Time Series” but the metric column doesn’t seem to work with the State Timeline
I have exactly the same problem. I expected that the metric column would define the swim lanes dynamically. This would be the same behavior as line graphs on a Timeline panel.
Registred to + to that
Got table with events statuses and entities
Exact entities is unknown in advance - have to use repeat pannel for each entity, want to see all of them on same graph in swimlanes
You have to read into the Multi-frame time series and how it works.
“Each frame has two fields: time, value”
“All Values are numeric” <— this is destroying both of us.
In Rotario’s case this works for him since his devices are numeric.
I have similar data to your columns ID and Max_state_s and it does not work.
This triggers the transformation for me:
SELECT
<timestamp_column> AS “time”,
case when <value_column>=‘x’ then 1
when <value_column>=‘y’ then 0
else -1
end as status,
job_name
FROM
WHERE $__timeFilter(<timestamp_column>)
The problem I encountered now is the data for values showing an infinite value:
@sgammma I’ve done as you said and it works for one series, but not another.
The timeline shows data in it, but all of the values are identical (even though I know there are different states). It seems to get messed up when I do the Group to matrix transformation.
My data in the table view looks fine, and the only difference I can see between the working and not working one is that the not working one has multiple series (or frames) appear in a dropdown, so I need to use the Merge transformation
No problem @sgammma . One thing I did notice is that “Grouping to matrix” reduces the table down to a point every 5 seconds (coincidental wit the Panel’s min-interval). If I change that to 1s, I get more data points, but they are still all the same state value.
I wonder if there are just too many points, and it is selected the last for each time interval.
I think I just solved my problem. I had misunderstood the series. It was reporting all states, and the value field indicated whether that state was enabled. So I just needed to add == 1 to my query so I get the actual state.
Maybe someone finds this handy. I could not make this work, so I have made little bit easier workaround.
I have created dashboard variable that is hidden and which is a query type and that gets all ids in selected time interval. It needs to have “all” allowed. After that I created state timeline graph repeating on that variable. It has a little caviate of having it in separate graphs, but results look almost the same.
