Find and show values of a field, which is also in another field

Elastic Stack Kibana
1

Hi all,
I try to explain my problem as best as I can. Please ask if it is still not clear:
So, I am sending me logfiles of two different systems via filebeat and parse them via logstash.
The documents of "ServerA" contain an interessting field called "FieldA" with "ValueA"
The documents of "ServerB" contain an interessting field called "FieldB" with "ValueB"
All documents are in the same index (filebeat-yyyy.mm.dd) the kibana index pattern is filebeat-*
I now need a search, where first) all Documents are shown, where FieldA exists and second) based on the ValueA of FieldA every Document where ValueB of FIeldB is as same as ValueA

So basically a Kibana (pseudo) search like:
FieldA: * AND FieldB = FieldA

In MySQL it would be sth. like a join. How can I do this in Kibana?
Thanks a lot in advance!
Cheers,
Marcus

2

Hi @MarcusCaepio, thanks for reaching out. You could do this with a scripted field that becomes true if FieldA equals FieldB. Then you can filter in your dashboard by creating two filters FieldA equals ValueA and ScriptedFieldAisB equals true to get the documents you want to match.

3

Hey flash, thanks a lot. will have a look on it tomorrow :slight_smile:

5

So I think I need a little help here. I tried to create a scripted field like this:

  • name: sc-test
  • language painless
  • type boolean
  • script:
    if (doc['FieldA'].value == doc['FieldB'].value) {
    return true;
    } else {
    return false;
    }

also tried just
doc['FieldA'].value == doc['FieldB'].value
But after saving this, in Discovery I just only have the sc-test field available with no matches. All the other fields are gone until I delete sc-test again.

Maybe an important info. FieldA and FieldB are in different documents, not in the same.

6

Hi @MarcusCaepio,

I'm sorry, I misread your original post - this will indeed only work if the fields are in the same document.

This kind of querying is very hard to do with Elasticsearch in general. A way that often works is to basically do the join at ingest time, meaning when ingesting the data you look up the values you are going to filter by and ingest them together with the rest of the document. Unfortunately that requires a bit more setup than just running filebeat, most likely you need a custom shipper that handles this. See also these resources: https://www.elastic.co/blog/managing-relations-inside-elasticsearch Does document database means denormalize

I'm sorry I don't have a better answer for you here.

7

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