topic DQL - get percentile of a value in DQL

DQL - get percentile of a value

paolo_fumanelli — Tue, 10 Dec 2024 07:02:49 GMT

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

Re: DQL - get percentile of a value

krzysztof_hoja — Tue, 10 Dec 2024 08:59:17 GMT

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)

Re: DQL - get percentile of a value

paolo_fumanelli — Tue, 10 Dec 2024 14:11:05 GMT

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