Show results only with matching fields from two different queries

[balazshajdu]

I am trying to combine the results from two queries, one using dataset=xdr_data and one from preset=xdr_file. But, I only want to see the results when the same "agent_hostname" appears in both queries. In other words IF agent_hostname from filtered xdr_data = agent_hostname from filtered preset, show me all the results with those agent_hostnames from each query. 

How would I go about staging this?

Thanks

[zarnous]

Hi @balazshajdu 

You can use the Join stage to do so. The join() stage combines the results of two queries into a single result set.  The default type is inner, 

inner: Returns all the records in common between the queries that are being joined. This is the default join type.

However, not sure if your example is just for demo or it is your actual use case, the reason is that agent_hostname filed is the same in both sources you have in reference, this is because preset = xdr_file is part of the bigger dataset dataset = xdr_data , so i recommend doing the matching on fields other than the hostname, but nonetheless, whatever field you choose you can use the syntax below and changes it accordingly to fit your use case

dataset = xdr_data
// here add your filters/ stages, the next step is to do the Join as below

| join type = inner ( preset = xdr_file ) as presetjoin presetjoin.agent_hostname = agent_hostname
| fields agent_hostname , * // here define the fields you want to show in your result table

Hope that helps and shed some light, please let me know if any and if that answers your question, feel free to mark this as accepted solution so others can benefit from.

Best,
Zee