PromQL query for 95th percentile on a timeseries that is in BPS is wrong

[aredz]

Timeseries: Gauge Type
Time Range: 30 Days
Data Source: Mimir
Visualization: Stat

Current PromQL:
quantile_over_time(.95,sdwan_edge_bps_total{serviceId="$ServiceId"}[30d])

Context:
As you can see on the Stat panel Area (the actual data points it goes up and down).
It seems like the promql I did gets the 95th percentile of the linear data. it doesn’t sort the data from lowest to highest.

sort() doesn’t work in grafana as well within the promql

Sample Screenshot:

Expected Result:
get the 95th percentile of the data (sorted from lowest to highest)

Question:

Is there a proper PromQL statement I can use?
or
Is there a way to use Transformation in Grafana, to Sort the timeseries. then get the 95th percentile
or
Is there a promql statement where I can get the nth record

for example:
timeseries name: stat_a
number of data: 100
index number: 95

stat_a[index number]

then I will get that particular index (I’ll just use some math functions to like get the 95th row based on the record count within 30days)

or if there’s any other possible solution please

[codi639]

Hey @aredz

@aredz

Is there a way to use Transformation in Grafana, to Sort the timeseries.

Yeah I think you can use Grafana to sort the time series data in ascending or descending order by using the sort by transformation (I think…). Once the data is sorted, you can run your query.
Try with and without the sort_desc or sort function, I wanna know how it goes with.

quantile_over_time(.95,sort_desc(sdwan_edge_bos_total{serviceId="$ServiceId"}[30d]))

Let me know how it goes.

Regards

[aredz]

Hi @codi639 ,

Thanks for replying. I actually tried that already. but there’s an error

bad_data: invalid parameter "query": 1:29: parse error: expected type instant vector in call to function "sort", got range vector

I also tried the sort (transformation) and it doesn’t do anything

what’s interesting though. If I did a 100th percentile. it’s sorting

query: (100th percentile)
quantile_over_time(1,sdwan_edge_bps_total{serviceId="$ServiceId"}[30d])

could it be a bug?

[codi639]

What looks the result with the transformation and the 0.95 parameter but no sort function in your query?

[aredz]

this is how the 0.95th percentile data looks like (with or without the sort transformation)

this is how the 1th percentile data looks like

[aredz]

btw, the reason I’m saying it’s displaying wrong value. When I export the data in Excel (CSV), and tried to manually calculate the 95th percentile, it’s showing different result than what it is showing in grafana

also, here’s a better view of the result using grafana’s explore feature

0.95th percentile

here’s the 1th percentile

pretty weird

[codi639]

It’s pretty weird, indeed…

I’m curious, can you try with:

quantile_over_time(.05,sort_desc(sdwan_edge_bps_total{serviceId="$ServiceId"}[30d]))

[aredz]

I tried, it’s always failing on the sort function

bad_data: invalid parameter "query": 1:34: parse error: expected type instant vector in call to function "sort_desc", got range vector

I removed the sort_desc and used this promql instead

quantile_over_time(.05,sdwan_edge_bps_total{serviceId="$ServiceId"}[30d])

got this result

[codi639]

Yeah my bad I forgot to remove the sort function haha

I noticed in your value via CSV that the timestamp are really not the same between 1 percentile and 0.95 percentile. Can you check if that’s normal?
I also saw that in the 0.95th percentile the data looks sorted from highest to lowest and the opposite for the 1th percentile. Which, indeed will show a decreasing graph…

[aredz]

I don’t think it’s normal too, I did write up different panels as well

25th, 50th, 95th, 100th, and used a fetch all promql (actual timeseries data placement)

the fetch all query, just looks like this: (using grafana’s 30day filter)
sdwan_edge_bps_total{serviceId="$ServiceId"}

[codi639]

Can you try the same test using the reverse option of the transformation?

[yosiasz]

@aredz

When I export the data in Excel (CSV), and tried to manually calculate the 95th

There is your issue: excel, when importing data into it esp numbers and dates it has the reputation of munging and taking over data.
Please share a sample of the data you exported to excel

[aredz]

I’ve tried this too. it’s the same behavior

[aredz]

Thanks for replying as well @yosiasz . where can I post this? it seems I’m not allowed to post csv formats

[aredz]

don’t worry about this guys. Since I can’t make this work with a simple promql query, I just used a different visualization (Dynamic Text Panel), went back to being a dev and wrote a code to get the percentile. added some additional info as well (Percentage used against total speed)

now, it’s having the same result as manual testing via excel

Thanks so much for your time with this.

[aredz]

For future reference, I think it’s better if you have histogram data instead of gauge, then you can use the default stats panel and use histogram_quantile(.95, metric) for the promql statement.

For anyone who is using the same setup as I did. Timeseries (Gauge)

Install this plugin first: Dynamic Text Panel
Full plugin documentation: Documentation

you can copy the same thing that I did (modify below, however you like)

1. get all data (30 days)
   
   
2. use transformation to sort data
   
   
3. Dynamic Text Panel setup
   
   
4. Content

<div id="percentile-container">
  <div id="panel">
    <div>
      <span>{{ q95 data }}</span>
      <span id="unit">Mb/s</span>      
    </div>
    <div>
      <span id="percentage">
        {{#each (variable "MaxDownload")}}
          {{ utilisation this }}% utilisation
        {{/each}}
      </span>
    </div>
  </div>
</div>

5. Javascript

var percentile = 0;
var max = 0;

handlebars.registerHelper("q95", (data) => {
  const row = (data.length - 1) * .95;
  const index = Math.round(row);
  const record = data[index].sdwan_edge_bps_total / 1000 / 1000;
  const rounded = record.toFixed(1);
  percentile = rounded;
  return rounded;
});

handlebars.registerHelper("utilisation", (licenseSpeed) => {
  max = licenseSpeed / 1000;
  const percentage = (percentile / max) * 100;
  return percentage.toFixed(2);
});

6: CSS

 #percentile-container {
   display: flex; 
   align-items: center; 
   justify-content: center; 
   flex-grow: 1; 
   flex-direction: row;
   max-height: 85px;
   overflow: hidden;
 }
 
 #panel {
   margin-top: 30px;
   margin-bottom: 0px;
   padding-bottom: 0px;
   font-size: 50px; 
   font-weight: 500; 
   line-height: 1.2; 
   position: relative; 
   z-index: 1;
   text-align: center; 
   color: rgb(163, 82, 204);
 }
 
 #unit {
   font-size: 26px;
 }
 
 #percentage {
   font-size: 15px;
   color: rgb(86, 166, 75);
   vertical-align: text-top;
 }