How to have a summary table with differents single metrics inside from the whole timeFilter range?

I want to have a summary table , with single max values from different metrics / measures inside on each line.

From a $timeFilter Range, i would want:

  • a table line with max value of metric1 from the timeFilter range
  • a table line with max value of metric2 from the timeFilter range

etc.
Is it possible?

i tried the “group by $timeFilter syntax” but it does not work (no data)

Hi,

Are you using mysql or postgres? Could you please include the query that doesn’t work and/or the table schema you’re trying to use.

Marcus

no it is influxdb and finally, i won’t have to do that.

Do I understand you correctly that you solved the problem?

If not, you can just create two queries with a max aggregation of each value and then select table transform=time series to columns mode.

Marcus

i fact, i just want to have single values by line. Like with single stat, but one a line.

Exe:
line 1: sum of metrics
line 2: max of metrics

etc…

Have a look at this demo dashboard. The key is to use the time series aggregation in the options tab.

thanks . i tried this. but i have a strange behaviour:

Comparing the table below the graph, and the summary table made with your solution, when i change time range:

  • if the range is not too large, the 2 tables give same values
  • when the range grows:
    • the min,max,avg,current values of the graph table are the good one, the ones of the summary table are totaly false: the max max gave me values like 21k (for query metric) since the max values in metrics is 2k.
  • the total value of the summary table is always good, while the the total value of the graph table became totaly false (srinking more and more)

Could you please include the queries (screenshots) you’re using for both panels. Think you may mix things up with aggregations done by influxdb and aggregations done by Grafana in the user interface.

Summary table:

graph:

You’re mixing sum per time interval (summary table) with max per time interval (graph) so it’s not strange that the values are different between the two. Please use the same aggregation for both summary table and graph and the numbers should add up.

ok. i will use the same aggregation on the graph than in the summary when i get the good values on the summary table.

For the moment, i am unable to find the real min, max, total in the summary table.

if i use sum, total is good, never min, max.
If i use max aggregation, min/max/avg are ok, not total: it shinks when time ranges grows.

How to have the min,max,avg,total ok ?

Then I would suggest you to to all the aggregations in influxdb - I added another panel in the demo dashboard. Key here is to hide the time column and use time series to rows.

ok now i have good values in the summary table.

1 Like

But what is the root problem, why can’t i use the min/max/total values with grafana?

Using your solution, the look is not so good, and it is worse if i add other metrics in the same table, all is mixed badly. I would want to use the nice grafana’s features.

I added an example at the bottom of the dashboard with a graph and a summary table. The aggregations adds up in that example. Could it be that you have null values in your series? In that case please change to fill(0) instead of fill(none).

there is no null values. There is exactly one value per second. It is an import from a formatted log file, into influxdb.

when we check manually the sum and the max we have these values :
cat check_mongo.log.28 | awk ‘!/Oct/ && ! /query/ {print $2}’ | xargs | awk ‘{gsub(/ /,"+"); print “(”$0")/1000" }’ | bc -> 12,441 millions
check_mongo.log.28 | awk ‘!/Oct/ && ! /query/ {print $2}’ | sort -n -u -r | head -n 1 -> 2592

With dashboard configured as you said:

image

we have theses values:

image

Max is ok, not total

How do you verify the values are the good one on your dashboard?

I just compare the summary table numbers with the graph panel legend and they add up. Are you sure that you’re looking at same time range in dashboard as when “awking” your log file?

Also, please make sure you’ve configured Min time interval to your write frequency:

image

You can also set Min time interval in metric options per panel.

As a quick alternative you may be able to change group by time($__interval) to group by time(1s) if 1s is your write frequency.

i have changed group by time 1s and it works!

image

but i don’t understand why i need to do that.

shouldn’t definitions be independent from write frequency?

That’s great.

Probably because you used max aggregation and didn’t receive a point each second before. Now when you group by 1s you get all the points and the total is just a sum of all points - please remember that when using time series aggregation in table panel the aggregations are done in the browser after points returned from influxdb in this case.

ok. In fact it is now very slow, and unusable…Graph needs 20 seconds on a range 3 days to build.