Getting this error with Microsoft SQL server : failed to convert long to wide series when converting from dataframe: long series must be sorted ascending by time to be converted

Grafana MSSQL
,
1

Hi Team,

I am using grafana version of 8.4.4. I am getting below error with Microsoft SQL Server as data source in stat panel. I have requirement where I want to represent values in ascending order. I am using this query to fetch records from DB.
SELECT $__timeGroup(moment,‘5m’) as time,
avg(res_is_available) ,
res_name
FROM
audit_health
GROUP BY
$__timeGroup(moment,‘5m’),
res_name
ORDER BY
avg(res_is_available) asc,
time

Capture
Capture1327×542 125 KB

I am able to get data from DB if I am removing the avg(res_is_available) field from order by clause. But requirement is to display values in ascending order with avg(res_is_available) field. Can anyone please help in this?

result
result1326×594 168 KB

1 Like
2

Take out the order by Time and change the Grafana setting under the Query “Format as” to Table

You only need the Time Series if you want a mini graph inside the Stat Panel

Hope this helps!

3

Hi @T_GrumpyEngineer,

I already tried that approach but I am getting below output through that query which I don’t want. I want to show all res_name in graph.

result2
result21327×592 109 KB
.
Could you elaborate more if I am doing anything wrong?

4

Ok I’m doing someting similar just excuse the hidden sub-select

Sometimes I force Grafana to see the metric and value same as you’re doing for time

image
//////////////////////////////////////////////////////////////////
image

And then for the Grafana format settings on the side

image
image341×726 28.7 KB

Think you were calculating Average on SQL and then Average on Grafana too

Hopefully this is it

1 Like
5

Hi @T_GrumpyEngineer,

Thanks for your reply. I want to represent the same way like first get average from sql and then represent the avg through grafana. My only concern is that I want to do order by it’s value using same panel and format of timeseries. Is there any way to do it?

6 
SELECT    res_name
         ,AVG( res_is_available )
FROM      audit_health
WHERE     moment > DATEADD(MINUTE,-5,moment)
GROUP BY  res_name
ORDER BY  AVG( res_is_available ) DESC -- or ASC instead

Grafana settings to Format as Table, Value Options . Show > All Values

To get the 6 orignal results unchanged by Grafana, otherwise you’re Averaging an Average from SQL and Grafana thinks you want 1 result back

2 Likes
7

Hi @T_GrumpyEngineer,

Thank you so much for your help. I managed to get that result back using this approach.

1 Like
8

Great! You’re welcome!

9

Also recommend you try this out by using a numeric value pointing to the column #. Calling a SQL function in ORDER BY might be a performance issue especially if you do not have an index on it then your dashboard does not lag.

SELECT    res_name
         ,AVG( res_is_available )
FROM      audit_health
WHERE     moment > DATEADD(MINUTE,-5,moment)
GROUP BY  res_name
ORDER BY  2 DESC
1 Like