Count unique combination of fields

Hi,

For some time I am searching now, but cannot find if what I need is possible. I have this index with several fields that are aggregatable. I want to fill a table with these fields, one row per unique combination of fields. The events in ElasticSearch als have a duration time in the same event, so for these events I als want to have columns giving me the lowest duration, highest duration and average duration of this specific combination.

So let's say the events are as following:

request : url                     : device   : duration(ms)
GET     : /first/second/third     : android  : 20
GET     : /first/second/fourth    : android  : 20
GET     : /first/second/third     : android  : 31
GET     : /first/second/third     : android  : 33
GET     : /first/second/third     : ios      : 20
GET     : /first/second/fourth    : ios      : 30
GET     : /first/second/third     : ios      : 30
POST    : /first/second/third     : android  : 20
POST    : /first/second/fourth    : android  : 20
POST    : /first/second/third     : android  : 31
POST    : /first/second/third     : android  : 33
POST    : /first/second/third     : ios      : 20
POST    : /first/second/fourth    : ios      : 30
POST    : /first/second/third     : ios      : 30

Then I want a table back with:

Nr. of occurrances: Call                                    : min. dur. : avg. dur. : max. dur.
3                        : GET /first/second/third/android   : 20       : 28        : 33
1                        : GET /first/second/fourth/android  : 20       : 20        : 20
2                        : GET /first/second/third/ios       : 20       : 25        : 30
1                        : GET /first/second/fourth/ios      : 30       : 30        : 30
3                        : POST /first/second/third/android  : 20       : 28        : 33
1                        : POST /first/second/fourth/android : 20       : 20        : 20
2                        : POST /first/second/third/ios      : 20       : 25        : 30
1                        : POST /first/second/fourth/ios     : 30       : 30        : 30

I know there are more questions like these on the forum, but since most of these are quite old I'm hoping on newer insights.

The Data Table visualization works great for this. Here's what you'll want:

In the Metrics panel:

  • Add a Count metric (should be there by default)
  • Add Min, Max, and Average metrics for your "duration" field.

In the Buckets panel:

  • Add a "Split Rows" bucket for each field you want in your unique combos (request, url, device).

In this format, you will get a separate column for each bucket. If you'd like to make these a single column, you could create a "scripted field" for your index pattern that concatenates these 3 fields together and then use that for your split rows bucket.

Let me know if you need any additional help!

Thnx. Will try monday (00:48 AM here now :wink:

Grtx

TypeApp voor Android downloaden

Op 20 apr. 2019, om 00:07, Josh Dover via Discuss the Elastic Stack elastic@discoursemail.com schreef:

That works awesome.
Now if I may ask some more questions...

  1. Can I give it a default descending order on the count column?
  2. Is it possible to move that Count column to the beginning? Make it the first column? Now the first columns are the splitorw thingies and the metrics are set after that.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.