In Canvas I'm attempting to query an index, return the results of a specific column, and then query another index with the results of the original query. An example would be two indices:
Index 1: login_activity; two fields: user.name, host.hostname
Index 2: machine_activity; 4 fields: host.hostname, host.ip, field3, field4
I'm attempting to determine which hosts USER1 has logged into, then pull all documents from "machine_activity" that have a matching host.hostname.
Elasticstack 8.6.2
kibana
| essql query="SELECT host.hostname FROM \"login_activity\" WHERE user.name='USER1'"
| joinRows column=host.hostname
| var_set name=hostnames
| essql query= "SELECT host.hostname FROM \"machine_activity\" WHERE host.hostname IN {var name=hostnames}"
| table
| render
I also tried
// | essql query= "SELECT host.hostname FROM \"machine_activity\" WHERE host.hostname IN \{var name=hostnames\}"
And
// | essql query= "SELECT host.hostname FROM \"machine_activity\" WHERE host.hostname IN ?" parameter={var name=hostnames}
I'm using joinRows and var_set because essql accepts filters, not datatables, and I'm hoping that passing the string as a variable will allow me to bypass that limitation.