Using essql query results a second query against another index

[dreynolds (Dan)]

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.

[nickpeihl]

Hi Dan. I think one way to pass in the variable is to use the string function to create the SQL string.

Maybe something like this?

| essql query={string "SELECT host.hostname FROM \"machine_activity\" WHERE host.hostname IN (" { var hostnames } ")" }

[dreynolds (Dan)]

Thank you Nick, that works perfectly.