How to Display Results in Table that are "Intersection" of two Queries?

Elastic Stack Kibana
, ,
1

Hi all,

we have the following use case:

We have an index containing logs of authentications from different devices. The data looks something like this:

device_id region_id district_id
1234 A district-1
5678 A district-3
1234 B district-5
9876 B district-2
9876 A district-5
1234 B district-6
9876 C district-8

Now we want to display the devices that have authenticated in BOTH regions "A" and "B" and also see the respective districts on a Kibana Dashboard.

Basically something like this:

device_id region_id disctrict_ids
1234 A district-1
1234 B district-5, district-6
9876 A district-5
9876 B district-2

Alternatively, it might also be sufficient for us to filter for all logs about devices that have authenticated in BOTH regions "A" and "B", like this:

device_id region_id district_id
1234 A district-1
1234 B district-5
1234 B district-6
9876 B district-2
9876 A district-5

We haven't found any solution to this yet. We also thought about using vega or transforms but did not really get ahead with this.

Thank you!

2

Anyone any idea? :slightly_smiling_face: If I can provide any more information, please let me know.

4

Are you interested in the device_ids who authenticated to multiple regions or also the district_id they authenticated into?

Because as for the former I think you can build a table in Lens using the Collapse by feature over a region_id dimension to have something like:

device_id | count_over_regions
1234      |        2
9876      |        2
5678      |        1

I think with Vega it would be possible to build something more specific, but it take way more effort. Knowing exactly what you need would help to share the answer here.

5

Thank you for your reply, @Marco_Liberati !

We are interested in both the device_id s who authenticated to multiple regions AND the district_id they authenticated into. I have included two tables how that could look like in my initial question. Option A) is one row per device_id and region_id and showing all district_id s as an array. Option B) is basically one row per device_id, region_id and district_id.

Regarding collapse by:

This feature does not seem to be available yet in 7.16 which is the version we're currently using - sorry for not mentioning the version in my initial question.

We're planning to migrate to 8.x in the near future but I am not sure if collapse by will work anyways for our use case (e.g. it only seems to work for numeric fields). But I will keep this in the back of my head.

Regarding Vega: Can you give me a hint how that could be implemented in Vega? I have read that Vega does not support tables and am not sure how to display the required information in any other format.