Combine two index in one using Kibana

Elastic Stack Kibana
1

Hello,

I have a requirement to combine two index in Elasticsearch on basis of below criteria -
index1:
Record1: {"service": "abc", "opened_at":"2024-11-04T16:48:04.000Z","closed_at":"2024-11-05T18:00:10.000Z")
Record2: {"service": "abc", "opened_at":"2024-11-06T16:48:04.000Z","closed_at":"2024-11-07T18:00:10.000Z")

index2:
Record1: {"service": "abc", "opened_at":"2024-11-06T16:48:04.000Z","closed_at":"2024-11-17T18:00:10.000Z")
Record2: {"service": "abc", "opened_at":"2024-11-10T16:48:04.000Z","closed_at":"2024-11-17T18:00:10.000Z")

So I want to correalet both of them on basis of service for example service and closed time on index 1 correlate to index2 service same and open time should be more than the closed time in index1 and less then the closed time of next record .
In that case index1 record1 will correlate to index2 record 1 .

Please suggest.

2

Hi @amity.nidhi Welcome to the Elastic community. Do you want to fetch all records which satisfying above conditions? or do you want to perform some calculation ? You can simply perform query on both the index index1,index2.

3

I want to fetch all records satisfying this condition and present it in dashboard

4

You can achieve this using script query. Could you help us with your index mapping and sample document index query. I will try to build query accordingly. thanks

5

Hello @ashishtiwari1993 , Sorry I dont have much experience with elasticsearch . Can you please help to elaborate.

6

Can I use python for this purpose ?
But i am not sure where should i schedule

7

Hello @ashishtiwari1993 , can you please help to share some example . the data i shared in my post is a sample data itself.

8

HI @amity.nidhi, Could you sample data as you mentioned above but still it is unclear to perform poc. Let's assume you've below records -

POST index1/_doc
{
  "service": "abc",
  "opened_at": "2024-11-04T16:48:04.000Z",
  "closed_at": "2024-11-05T18:00:10.000Z"
}

POST index1/_doc
{
  "service": "abc",
  "opened_at": "2024-11-06T16:48:04.000Z",
  "closed_at": "2024-11-07T18:00:10.000Z"
}


POST index2/_doc
{
  "service": "abc",
  "opened_at": "2024-11-06T16:48:04.000Z",
  "closed_at": "2024-11-17T18:00:10.000Z"
}

POST index2/_doc
{
  "service": "abc",
  "opened_at": "2024-11-10T16:48:04.000Z",
  "closed_at": "2024-11-17T18:00:10.000Z"
}

Now on which condition you expecting which documents ? Correlation we can do basis on service name but could you give more explanation around your conditions?

9

Hello Ashish ,
I want to combine both records when closed_at in index1 is less that in index2

For Example :-
record in index1 doc1 have close_at time 2024-11-05T18:00:10.000Z which is less than opened at time in index2 doc1 so to create index3 with additional field in index2
POST index3/_doc
{
"service": "abc",
"opened_at": "2024-11-06T16:48:04.000Z",
"closed_at": "2024-11-17T18:00:10.000Z"
"dependentonindex1" : "yes-reference-record-from-index1"
}

Similarly it should correlate others.

10

Here are some sample Data -

Index 1:

``index 1
1.{
"_index": "change_test",
"_type": "_doc",
"_id": "adqddqw",
"fields": {
"opene_at":
[
"2024-12-01T23:00:46.000Z"
],
"state": [
"Closed"
],
"closed_at": [
"2024-12-02T02:00:46.000Z"
],
"business_service":
[
"test1"
],
"change_number":
[
"CH123"
]
}
}

{
"_index": "change_test",
"_type": "_doc",
"_id": "adqddqw",
"fields": {
"opene_at":
[
"2024-12-02T23:00:46.000Z"
],
"state": [
"Closed"
],
"closed_at": [
"2024-12-03T23:00:46.000Z"
],
"business_service":
[
"test1"
],
"change_number":
[
"CH456"
]
}
}`

Index 2

{
"_index": "incident_test",
"_type": "_doc",
"_id": "adqddqw",
"fields": {
"opene_at":
[
"2024-12-02T03:00:46.000Z"
],
"state": [
"Closed"
],
"closed_at": [
"2024-12-02T04:00:46.000Z"
],
"business_service":
[
"test1"
],
"incident_number":
[
"INC321"
]
}
}

In this case create index3 with correlation as business service and closed_at in index1 should be greater that doc1 but less than doc2.
so that first record in index2 correlate to only first record in in index1 not not second record