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?
    bucket AS “time”,
    device_id AS metric,
    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?
    State timeline | Grafana documentation

1 Like

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


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.


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:

<timestamp_column> AS “time”,
case when <value_column>=‘x’ then 1
when <value_column>=‘y’ then 0
else -1
end as status,
WHERE $__timeFilter(<timestamp_column>)

The problem I encountered now is the data for values showing an infinite value:

I had the same problem, and solved it applying the following transformations:

  1. Grouping to matrix → Column=device_id, Row=time, Cell Value=status
  2. Convert field type → Field=Time\device_id as Time

Then just use the plot to plot the results.

@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

Do you know what the issue could be?

I am sorry, I have no idea :frowning:

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.


kube_pod_status_phase{namespace="$namespace, pod=~"my-app-prefix-.*"} == 1

If you leave the query format as Timeseries, you need two additional transformations at the start:

  1. Merge
  2. Labels to Fields (pick the two labels you want to use in the Group to matrix transformation)
  3. Grouping to matrix (as per post by @sgammma)
  4. Convert field type (as per post by @sgammma)

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.

1 Like

For me, adding a “Partition by values” transform worked.