Devide result set

  • What Grafana version and what operating system are you using?
    Grafana 8.3.3

  • What are you trying to achieve?
    make task status time viewable

  • How are you trying to achieve it?
    I currently try to use the State timeline panel with a SQL query on a PostgreSQL database.
    My result set looks like this:
    |Task-1|New|2021-04-13 13:20|
    |Task-1|Closed|2021-07-27 15:14|
    |Task-1|Reopened|2021-08-02 09:24|
    |Task-1|Closed|2021-08-02 09:24|
    |Task-2|Request|2021-12-13 15:22|
    |Task-2|Dispatching|2021-12-14 08:32|
    |Task-2|Customer Service Investigating|2021-12-14 08:33|
    |Task-2|Resolution suggested|2021-12-14 13:09|
    |Task-2|Solved and Documented|2021-12-14 13:10|
    |Task-2|New|2021-12-14 13:19|
    |Task-2|Open|2021-12-14 13:26|
    |Task-2|In Progress|2021-12-14 13:26|
    |Task-3|New|2021-12-18 06:16|
    |Task-3|Open|2021-12-18 06:17|
    |Task-3|In Progress|2021-12-18 06:17|
    |Task-3|Finished|2021-12-18 20:54|

  • What happened?
    I get 2 rows task + status shown

  • What did you expect to happen?
    I expected 3 rows with the label Task-* and the status like in the example
    Grafana. The problem is that the amount of tasks is changing.


your provided table does not match the data model required for the state timeline, but it could.

Take a closer look at the official example that you cited. If you view the query editor in the table view format (which I highly suggest for debugging these panels) you will see this:

note how there are three queries, each pulling in a separate table, and that those three tables are not merged into ONE table, like your data. Basically, you need to destructure your data with one table per task (think of an unique series of data frames for each Task. Here is your data correctly mocked up on our sandbox instance. You will need to be logged in to view it:

One general problem: It will be difficult to visualize an entire year of data and also see state changes that occur <1s apart, as in these two. I modified the time to make those visible:

|Task-1|Reopened|2021-08-02 09:24|
|Task-1|Closed|2021-08-02 09:245|

Thanks a lot for the hints!
I’m afraid, I can’t view the page you mentioned.
Maybe my user is not allowed to view the page. My username in the sandbox is the same one I use here.

will this link work when you are signed in?

I’m still trying to troubleshoot why some users can view these dashboards on play while others can’t

Hi @mattabrams, I’m sorry I’m still not able to view the dashboard.
my current Organisation/role is “Play Grafana”, “Viewer”

1 Like

@robert8103 my apologies. I was putting the dashboards in a folder that had no viewer privileges. Try once more for me and it should work :+1:

1 Like

Hi @mattabrams, thanks a lot that was helping me further!
Do you know by heart is there a way to pull the SQL result set in a separate table?

1 Like

not sure about that, but there are some real SQL wizards here, so hopefully another member can jump in…:grafana: :laughing: