Help with complex DSL query/filter/grouping

[Bryan_Hamilton]

Hi all,
I have 2 seperate queries and am trying to find a way to filter and only return documents matching both queries.

Query01 = event.provider : "Microsoft-Windows-Sysmon" and event.code:"3" and destination.port:( "389" or "636")

Query02 = event.provider : "Microsoft-Windows-Sysmon" and winlog.event_id: "18"

Even though documents returned by these 2 queries have diffent field sets, they all have at least 2 fields in common: the process.executable and the process.pid. To illustrate this, if I combine these 2 queries with an or statement, the result would look something like this:

Now, I would like to use either the process.executable or the process.pid or both as a reference and only display documents with a process.executable found both in query01 and query02. So, ultimately every process.executable displayed will have at least one document with event.code = 3 and at least one other document with event event.code = 18.

My research so far indicates that I might be able to acheive this by using maybe compound or scripted query/filter in DSL but I haven't been able to find any exemple anywhere that would give me something start from.

Any help would be appreciated.

Thanks

[tsullivan (Tim Sullivan)]

Hi, this use case is very similar to a join, which isn't supported in Elasticsearch. Your best option would be to index the raw data into an entity-centric index that combines related data into a single document.

[Bryan_Hamilton]

Thanks @tsullivan , I will try doing that.