DQL - get percentile of a value

[paolo_fumanelli]

Hi DQLers, our tenant is strongly tag-oriented and we tagged almost each detected service with a key:value tag that allows us to assign each service to a specific application (and, in turn, to an application development team).

We're now building a dashboard that acts as an application scorecard with some kpi for each application: managed calls, topology (calls, called by), error rate etc...

I'd like to highlight the "weight" of this application among all monitored ones. So I'd like to measure the percentile of an application managed requests.

Practical example. Let's say we have 100 different applications and their managed requests in a timeframe (sort descending):
App_ABC 10 000
App_XYZ 8 500
App_QWE 7 000
...
...
...

App_ZXC 100

For application App_XYZ I'd like to find DQL code to get that App_XYZ represents 2nd percentile of applications managed requests.

How can I get this?

I know I can get the value of any percentile but in this case I need to work on the other side.

Any clue?

Thanks!
Paolo

[krzysztof_hoja · Accepted solution]

I think what you are looking for is an index of application in an array of all applications sorter by number of requests. And this index should be expressed not as a absolute value, but as percentage vs. whole array size.

Such query on test data looks like this:

data
  record(application="App_ABC", requests=10000),
  record(application="App_XYZ", requests=8500),
  record(application="App_QWE", requests=7000),
  record(application="App_ZXC", requests=100)
| summarize { d=collectArray(record(requests, application)) } 
| fieldsAdd d = arraySort(d, direction:"descending")
| fieldsAdd d = d[][application]
| fieldsAdd p = arrayIndexOf(d,"App_XYZ")
| fieldsAdd p = 100.0*p/arraySize(d)

[paolo_fumanelli]

That's great! It fits perfectly my use case. Thanks a lot