How does the Status History panel work?

[xbalayer]

Hi

On the grafana website there is a status history panel like the below

What does the data need to look like for this to work?

I’ve got a simple sql query that gives me

• server name
• timestamp
• value

or simply put, something like this:

select 
	systemKey
	,time
	,pr
from mytable 
where time between $__timeFrom() AND $__timeTo()

However, everything ends up squashed up

Table view:

[mattabrams]

Hi @xbalayer

I believe that the Status history panel expects data as a simple data frame (timestamp, single value). You will note on the example on play.grafana.org that each row in the status history is returning a distinct time series, and that series contains only timestamps and single values.

Try splitting your query up into multiple queries, where each returns the values for an invidual system, like this:

Note the dropdown when viewing this in the table panel, which means that there are multiple series to choose from. This is what you want to achieve. This renders like so in the Status History panel:

[xbalayer]

Thank you, that explains it well.

Unfortunately it won’t work for my specific use-case as I have a dynamic set of series and there is no way to “repeat” queries based on each series.

My workaround was to use a normal table with coloured cells by pivoting the query in SQL:

[terrancej]

Hello xbalayer, I’m trying to do something similar with some data I have as well. Is it possible you can share a little more details on how you did this. I have 3 fields similar I’m trying to show in my panel but i have no idea how to configure the table to show it how you have it above. Any assistance would be greatly appreciated.

[xbalayer]

Hi @terrancej

My data is in SQL Server, so this only really applies if you are using a SQL database. This can also be slightly improved code-wise with SQL Server 2019, but this will work either way.

Since I wanted to make the days dynamic both the columns in @cols and query in @query needed to be dynamic too.

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(CAST(timestamp AS DATE)) 
                    from vcom.dbo.system_daily_pr where timestamp between $__timeFrom() and $__timeTo()
                    order by ',' + QUOTENAME(CAST(timestamp AS DATE))
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT systemKey,' + @cols + ' 
			FROM 
            (
                select systemKey, timestamp, value
                from vcom.dbo.system_daily_pr

			) x
            pivot 
            (
                sum(value)
                for timestamp in (' + @cols + ')
            ) p '

execute(@query)

[terrancej]

Thanks xbalayer, I really appreciate it. Have a great day.

[tobias0]

You can use a transform. The SQL query returns 3 columns: time, metric, value. You need 2 columns: time, [value], where [value] has the contents of value but the column name of metric.

Use regular SQL query with 3 columns, then add transforms:

Grouping to matrix

• Column: metric
• Row: time
• Cell value: value

Organize fields:

• Rename the time column back to “time”

Convert field type:

• Field time as Time

[raghavi]

Hi Xavier Balayer,

Can you please share the Grafana Dashboard ID for this below one?
Status history panel which you have shown in the query?

What is the datasource for this dashboard?

I want to import the status history dashboard as shown with datasource as Prome theus. Can you help me with that?

[gijsbarmentlo1]

Hello,

I have managed to get it to look like you want, while writing only a single SQL query that returns time, value and servername.
I used the transform and split on the field that should be a row (ig. the backendN in the initial screenshot):

And my dashboard works well.