how to combine two searched by row which have some columns comman but not ids

[sachinkiet]

Hi,

i have two searches first give open alert data and second gives closed alert data i want to merge both results.

alert id	message	server	opentriggredtime
1	fsdf	127.0.0.1	01/09/20
2	fdsfs	127.0.0.1	01/09/20
closed id	message	server	closedtriggredtime
3	fdsfs	127.0.0.0	01/09/20
4	fsdf	127.0.0.0	01/09/20
alert id & closed id	message	server	opentriggredtime	closedtriggredtime
1	fsdf	127.0.0.1	01/09/20
2	fdsfs	127.0.0.1	01/09/20
3	fdsfs	127.0.0.0		01/09/20
4	fsdf	127.0.0.0		01/09/20

[sachinkiet]

Actually the data which I am getting are from two searches. you have used split function at line no 4, why we are using it.

[ITWhisperer]

The first part just creates sample data - you don't need this as you already have the data. The important parts are the renames

your first search
| rename "alert id" as id
| append [ your second search
| rename "closed id" as id
]
| table id message server opentriggredtime closedtriggredtime

[ITWhisperer]

Rename the id fields to the same name and append one search to the other

| makeresults | eval event="{\"alert id\":1,\"message\":\"fsdf\",\"server\":\"127.0.0.1\",\"opentriggredtime\":\"01/09/20\"}\n{\"alert id\":2,\"message\":\"fdsfs\",\"server\":\"127.0.0.1\",\"opentriggredtime\":\"01/09/20\"}"
| eval event=split(event,"\n")
| mvexpand event
| spath input=event
| fields - _time event
--- created first table
| rename "alert id" as id
| append [ | makeresults | eval event="{\"closed id\":3,\"message\":\"fdsfs\",\"server\":\"127.0.0.0\",\"closedtriggredtime\":\"01/09/20\"}\n{\"closed id\":4,\"message\":\"fsdf\",\"server\":\"127.0.0.0\",\"closedtriggredtime\":\"01/09/20\"}"
| eval event=split(event,"\n")
| mvexpand event
| spath input=event
| fields - _time event
--- created second table
| rename "closed id" as id
]
| table id message server opentriggredtime closedtriggredtime