How to extract median by using Kusto

Question

I figured out, that i need the percentiles function to extract the median. Still, i don't really get a result. I want to find out how many mails are filed on average without the outlier distorting the result. The customDimensions.AmountStored contains the information about how many mails got stored.

I'm using this query:

AllShards_CustomEvents 
| where name == "Mail.Implementation.StoreCount" 
| extend storeCount = toint(customDimensions.AmountStored)
| project timestamp, shard=tostring(customDimensions.ShardName), storeCount
| summarize percentiles(storeCount, 5, 50, 95) by bin(timestamp, 7d), shard

using `percentile(storeCount, 50)` will indeed give you the median. you may want to clarify what you mean by "i don't really get a result", as it's not clear to anyone reading your original message. — Yoni L., Mar 29 '20 at 15:50
@YoniLeibowitz every 2 minutes emails get stored. The amount of stored emails is saved inside the customDimensions.AmountStored parameter. So my median should look like 1000 mails per day for example... but at the moment it's either 0 or 1. — fabian09, Mar 30 '20 at 06:42

score 1 · Answer 1 · answered Mar 30 '20 at 16:58

Perhaps, I am missing something - but the query below seems to be working fine. Perhaps, you can clarify what exactly is not working for you?

let AllShards_CustomEvents = datatable(timestamp:datetime, name:string, customDimensions:dynamic)
[
    datetime(2020-03-30 16:55), "Mail.Implementation.StoreCount", dynamic({'AmountStored': 100}),
    datetime(2020-03-30 16:57), "Mail.Implementation.StoreCount", dynamic({'AmountStored': 200}),
    datetime(2020-03-30 16:57), "Mail.Implementation.StoreCount", dynamic({'AmountStored': 300}),
    datetime(2020-03-30 16:57), "Mail.Implementation.StoreCount", dynamic({'AmountStored': 400}),
    datetime(2020-03-30 16:57), "Mail.Implementation.StoreCount", dynamic({'AmountStored': 500}),
];
AllShards_CustomEvents 
| where name == "Mail.Implementation.StoreCount" 
| extend storeCount = toint(customDimensions.AmountStored)
| project timestamp, shard=tostring(customDimensions.ShardName), storeCount
| summarize percentiles(storeCount, 5, 50, 95) by bin(timestamp, 7d), shard

|timestamp|shard|percentile_storeCount_5|percentile_storeCount_50|percentile_storeCount_95|
|---|---|---|---|---|
|2020-03-30 00:00:00.0000000||100|300|500|

How to extract median by using Kusto

1 Answers1