TSVB or Lens visualization for monthly maximum of daily sum of a field value

[smondal (Shrayani)]

I have an index called global_gpu-* with @timestamp and "gpu_gflops" field among others. It simple enough to get the daily sum of "gpu_glops" using "formula" option in lens as follows:
sum(gpu_gflops)/1000/1000

I get the total peta flops for the day. What I want is to have a visualization showing maximum for month calculated over these daily total petaflop values.

E.g. 1st July - sum(gpu_gflops)/1000/1000 = 396
2nd July - sum(gpu_gflops)/1000/1000 = 340
:
:
:
31st July - sum(gpu_gflops)/1000/1000 = 355

the monthly maximum for July should be 396. This needs o be done over all the months of a year. How can this be achieved using Lens or TSVB in Kibana?

[stephenb (Stephen Brown)]

Hi @smondal

What version are you on....

This could be pretty easy with ESQL

My quick test...

FROM logs-* 
| STATS count = COUNT() by time = BUCKET(@timestamp, 1h)
| STATS daily_max = MAX(count) by day = BUCKET(time, 1d)

So yours would be something like

FROM metrics-*
| STATS daily_sum_gflops = (SUM(gpu_gflops)/1000.0/1000.0) by days = BUCKET(@timestamp, 1d)
| STATS monthly_max = MAX(daily_sum_gflops) by months = BUCKET(days, 1m)

And of course if this is a normal ongoing thing you could use a transform to pre-aggregate the data

[smondal (Shrayani)]

This worked perfectly. Thanks! I would also check out transform.

[stephenb (Stephen Brown)]

Cool, Keep ESQL in mind from here on out can make some things drastically simpler and other things which could not be done... doable.