How to filter an ESQL dashboard on a field in the index being looked up on

Elastic Stack Kibana
,
1

Hi, I'm having trouble finding a nice intuitive way to filter an ESQL dashboard. Any ideas would be greatly appreciated !

I have two indices: buildings and rooms, where each room has a building Id. On the dashboard I have a table of all our rooms, and thats simply:

FROM rooms

| LOOKUP JOIN buildings ON buildingId

However, I also have some other visualisations such as a table of each building, with aggregations about its number of rooms. To avoid missing out buildings with 0 rooms, these visualisations are sourced from the buildings index, and then with have a lookup join to the rooms index:

FROM buildings

| LOOKUP JOIN rooms ON buildingId

| STATS roomCount = COUNT(roomName.keyword),

| SORT roomCount ASC

These are the visualisations causing me issues, as buildings doesn't have a roomName field and so we can't add a normal field filter to the dashboard (those filters are applied before the lookup join so would result in 0 results)

I've had some ideas around ESQL control variables but none have worked so far... Using an ESQL control variable, say '?roomName' with the query:

FROM rooms

| STATS BY roomName

we can filter the records after the lookup join is completed:

FROM buildings

| LOOKUP JOIN rooms ON buildingId

| WHERE MV_CONTAINS(?roomName, roomName)

This has been looking promising but the issues lie with the limitations around the control variable. Firstly, a control variable must have a value so we cannot leave it empty when the user enters this dashboard. For intuitive use, the dashboard needs to essentially be "unfiltered" when first seen.

Ideally we could have the 'Select All' button be the default values of the filter, as we cannot expect the users to setup that up themselves (also, I am unable to simply add all the possible values as I don't know what rooms the users have). Unfortunately, I don't think this is currently possible.

I was wondering if we could have a phony options: "All Rooms", that when selected, we would have the esql not apply the room name filter. I think this would be possible with an OR statement. However, I cannot find how to add a static value of "All Rooms" to the results returned from the control variable query...

Any ideas anyone has on this problem would be very helpful, thank you !

2

Hello @TSlump

I am not sure if the understanding of requirement is correct but are you looking for below kind of graph :

image
image1911×934 56.4 KB

Both the Graphs with same ES|QL :

FROM rooms
| LOOKUP JOIN buildings ON buildingId
| STATS roomCount = COUNT(*) BY roomname, buildingname
| SORT roomname ASC

you can just change the X/Y-Axis to get different views.

Thanks!!

3

Thanks for your response @Tortoise

The only issue I have with using the rooms index as the source index is that buildings without any rooms will not be shown on these graphs. That is why I've been using the buildings index as the source and then lookup joining with the rooms index:

FROM buildings
| LOOKUP JOIN rooms ON buildingId

But then if you add a normal options filter for roomName, any visualisation with the buildings index as the source won't work.

I hope this explains my problem better, thanks !

4

Found a solution that is working well for me:

FROM buildings, rooms

| LOOKUP JOIN rooms ON buildingId

We used both indices as the source index. This way, all our visualisations include buildings that do not contain rooms, but all our visualisations can still be filtered by fields only present in the rooms index.

2 Likes