Matching data in two different indexes

Elastic Stack Kibana
1

Hi, team!
I collect authorising logs from two systems into two separate indexes. Index content:
Index1:
system1_timestamp
system1_username
system1_ip
...
Index2:
system2_timesamp
system2_username
system2_ip
...

I want find intersections on the following condition:
User authorised one day with one ip in these two systems
system1_timestamp_day == system2_timestimestamp_day AND system1_ip == system2_ip

On the output I want receive:
system1_username
system2_username
system_timestamp.day
system_ip

Now, to carry out this check, I use excel, which takes a lot of time(
Tell me if it is possible to implement this matching by the Elastic tools?
Thank you in advance!

2

Hello Oleg,

As far as I am aware joining two or more indexes is not possible in Kibana. Nevertheless, here are 2 options depending on your usecase:

Option 1 - search for a specific IP only
Are the field names only placeholders for clarity or the real names? If those are the real names I would advise to synchronize the fieldnames between both indexes (e.g. by using ECS):
Index1:
@timestamp
user.name
client.ip
...
Index2:
@timestamp
user.name
client.ip
...

After creating an alias and creating an index pattern for this alias in Kibana, it is now possible to search for data regardless of the index the data is stored in. Searching for client.ip: "127.0.0.1" would return all documents from both Index1 and Index2 where this IP was mentioned.

Option 2 - use Logstash to do what you want
To achieve exactly what you want, you could create a Logstash pipeline:

  • use Elasticsearch input to search all data older than 1 hour but younger than 2 hours (to make sure to not prcoess documents twice but still making sure that the data for index2 is already stored in Elasticsearch)
  • use Elasticsearch filter to enrich the document with data from Index2
  • use drop filter to drop all documents where no matching document was found in index2
  • use elasticsearch output to store the data in a new index

Now, you can search the new index exactly like you wanted.

I hope this helped pointing you in the right direction.

Best regards
Wolfram

3

Welcome to our community! :smiley:

Elasticsearch and Kibana cannot do joins dynamically like this. Another option is to run an ingest pipeline with an enrich processor to merge the two into a new index.

6

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