State Timeline schema

[rotario]

• 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?

• What did you expect to happen?
  
  

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/

[mattabrams]

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?

[rotario]

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

{ "request": { "url": "api/ds/query", "method": "POST", "dat -...

Thanks

[jochenrau]

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.

[m4xpaw]

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

[rotario]

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.

[dsfrederic]

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?

[mrsantamaria]

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: