State Timeline schema

  • What Grafana version and what operating system are you using?
    8.0.3, ubuntu 20.04

  • What are you trying to achieve?
    Get the schema right to display the state timeline for different devices correctly

  • How are you trying to achieve it?
    SELECT
    bucket AS “time”,
    device_id AS metric,
    status
    FROM device_status
    ORDER BY 1,2

  • What happened?

My table looks as below:

 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.

  • Did you follow any online instructions? If so, what is the URL?
    /docs/grafana/next/visualizations/state-timeline/

Hi @rotario,

Question, what if you split your query into multiple queries and return only device_1 and then device_2 individually:

Otherwise, share your data from the inspect panel and we can try something else?

Hi Matt,

Sorry for the delay in my response!

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

Please find the query inspector response below

Thanks

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

I’ve found a way to get this working! If you add a transformation and add “Multi-frame timeseries” you get the swim lanes you’re after.

2 Likes

This solution is not working for me…

This is my data

and finally enabled the transformation

As a result I get the message “No data in response”

Do you have an idea what’s wrong?

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: