Find data in 2 fields

Elastic Stack Kibana
1

I am trying to write a query that will find if the same data exists in two fields. What i want to do is search two logs to see if that data exists in each but in each log the field names are different.

Example:
Bro file logs names the unique identifier FUID and the email log names it FUIDS but I am trying to write a search that will return me both logs with the same identifier in each. In splunk I would do a join but that isn't a possibility (I don't think) in elastic. So what can I write in Kibana to get both logs returned when the data in each is the same.

Also, if I search for a specific FUID I get both logs, what I really want is a search that will return all logs that contain matching FUID and FUIDS. I am trying to identify all emails with attachments and see the email and attachment in one search without searching individual FUIDS.

Thanks. If I am not being clear please let me know and I will try to clear things up more.

2

I'm not 100% I understand the problem and use case. Could you give an example of what your documents look like, and how you would like to see the results in Kibana?

3

I have 2 documents with the same data in two different fields.

One is SMTP.FUIDS with a unique identifier that consists of a hash of the 5 tuple the other is EVENT.FUID with is again a has of the 5 tuple.

Each field contains the same hash because its the same file but one is in a SMPT document and the other is in a FILE document.

What I would like to do is write a search that looks for when the data in EVENT.FUID is equal to the SMTP.FUIDS and return both the FILE document and SMPT document in the same search so I can see all of the data related to the file which was received or sent via SMTP.

4

well you can obviously do SMTP.FUIDS:<id> OR EVENT.FUID:<id> to see all the docs with a specific ID, but it sounds like you want to see all of the search results, grouped by ID, is that right?

Would it be sufficient to sort the search results by ID so that all docs for a given ID are displayed next to each other?

If you want to combine all of the docs matching a given ID into a single search result hit, that's not something you can do post indexing. You'd need to denormalize the data before sending it to ES.

In the future it might also be possible to return search results grouped by a field by using the top_hits metric in combination with the terms agg.

Sorry if I'm still not understanding your use case exactly, let me know if I'm off the mark.

5

Thank you for the response. My use case is I don't know which document ID I want to search for. I want to run a search that will look for matches in the SMTP.FUIDS and EVENT.FUID fields and return all those documents. The specific use case is I am monitoring email traffic and the emails and attachments are split into different documents. I want to run a search that will look for any SMPT document that contains the field SMTP.FUIDS and any FILE document that contains a matching EVENT.FUID and returns all of those back.

This search would not be tied to one search (I can search any of the SMTP.FUIDS OR EVENT.FUID and get the documents for that search) but I want a search that will be global and search all documents for matches and return all of them. The idea is I would be able to see the documents that contains an emails and their corresponding attachments on the same screen

is it possible to write a search that says return results if EVENT.FUID == SMTP.FUIDS. When I try that I get no documents returned even though I know they exist.

6

Unfortunately it's not possible to do a "join" like this today. I filed an enhancement request on our Github repo with an idea that I think might be a solution to your problem: https://github.com/elastic/kibana/issues/9020. Please feel free to add any thoughts you might have about the solution to the comments.

With Kibana today there are just a couple options. When searching you could sort by FUID which would at least place all relevant docs next to each other in the list. But our current lack of multifield sorting in Kibana might prove to be a problem if you want to sort by date as well. The only other option is to include all relevant info in a single document.

7

Thank you for the help

8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.