How to enrich data and filter by this data with Canvas?

[rmartinez (Raul)]

Hello,

We have a use case four our dashboards and it's not clear the feasibility.

We have IoT devices gathering and indexing information from our customer locations. In these locations we want to measure the effect in users of some special event, for example promotions.

We were asked to build filters on top of canvas to filter information based on these static facts. Putting everything together....

• IoT devices gather usage information in our customer locations
• Customer runs promotions in some of the locations and want to see the effect in canvas dashboards
• We want to inject static data like, PromotionA was run in locations A,B,C and PromotionB in locations D,E,F
• In the indexes we can filter by location, this fields is already available
• How can we filter in canvas by Promotion? Can we inject the static content in a different index and then run something like....

select <event_fields> from <iot_devices_filter> where location in <select location from auxiliar_index where promotions_run contains PromotionA> ?

SQL, DSL... whatever language. I just used SQL to make it easier to understand.

Any help would be welcome! Thanks in advance.

BR,
Raul

[wylie (Wylie Conlon)]

The easiest way to do this is to create a single index that contains all the data you're trying to search in the same place, because joins are expensive no matter how you do them. There is a way to do this in Canvas by generating a query string from the results of a previous query, something like this:

kibana 
| filters  
| essql query="SELECT geo.src FROM kibana_sample_data_logs" count=10
| joinRows column="geo.src"
| var_set name="query" value={string "SELECT geo.dest FROM kibana_sample_data_logs WHERE geo.dest in (" {context} ")"}
| var name="query"
| filters
| essql query={var name="query"}