Use "wide-format" data for State timeline?

Grafana MSSQL
,
1

  • What Grafana version and what operating system are you using?
    v8.3.1
    Windows

  • What are you trying to achieve?
    I’d like to use the State timeline to visualise when some series are active. The trick is that the data looks slightly different from what seems to be the expected format.
    Instead of
    |<timestamp>|<metric_name>|<metric_value>|
    It is formatted as
    |<metric_name>|<timestamp_start>|<timestamp_end>|
    The metrics only have 2 values, active or inactive, so each row corresponds to one activation and ensuing deactivation.

  • How are you trying to achieve it?
    I’ve tried to unpivot the data but this doesn’t populate the <metric_value> column, which is still expected by the visualisation. I could try to somehow build the query so that it generates a 0,1,0,1,… sequence for this column but I wonder if this is the best way to go about this.

  • What happened?
    The furthest I got was to add a
    ,1 as value
    so that the visualisation is at least generated, but it’s clear that (as expected) it’s not looking as desired.

  • What did you expect to happen?
    Ideally I’d expect this visualisation to, when provided with data that has 2 timestamp columns and no value columns, imply that these correspond to start and end times and plot accordingly.

  • Can you copy/paste the configuration(s) that you are having problems with?

    image
    image1263×778 37.6 KB

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    No

  • Did you follow any online instructions? If so, what is the URL?
    I couldn’t find any but happy to follow if there are.

1 Like
2

What is your data source?

I was unable to use this visualisation with Postgres as a datasource, because it only works with time-series formatted data. But any relational database would give you data with one row per label instead, so you have to pivot the data – which is OK if the set of labels is known in advanced, and otherwise essentially impossible.

I also tried using the transformation " Prepare time series", but that only works if the values are numerics, which they aren’t in this case.

Same challenge trying to use the Bar Chart plugin with a relational database.

1 Like
3

The data is pivoted, I suppose the solution involves unpivotting, which is what I had attempted to do