pass variable and value to subsearch

[Qingguo]

Hi All

I have a question and need to do the following:

Search contidtion_1 from (index_1 ) and then get the value of field_1 and the value of field_2.

then search the value of field_1 from (index_2 )  and get value of field_3.

I want to have a difference calculation  between  value of field_2 and value of field_3.

It it possible to  achieve this using a single query?

[Qingguo]

Thanks for your reply , 

here are more detailed requirement, I've got some logs I need to join and put on the same row and difference values .

Index1 events: (base query: index=index1 playbook=100)

"Playbook":"100","update_time":"2021-09-27T10:51:16.572759Z","container":"1497"
"Playbook":"100","update_time":"2021-09-27T10:52:16.572759Z","container":"1498"
"Playbook":"100","update_time":"2021-09-27T10:53:16.572759Z","container":"1499"
........

Index2 events (base query: index=index2 😞
"container":"1497","start_time":"2021-09-26T8:53:16.232759Z"
"container":"1498","start_time":"2021-09-25T8:53:16.232759Z"
.....

Desired output:

container start_time update_time time_diff(update_time-start_time)
1497 2021-09-26T8:53:16.232759Z 2021-09-27T10:51:16.572759Z xxx
1498 2021-09-25T8:53:16.232759Z 2021-09-27T10:52:16.572759Z xxxx
1499 ....

Appreciated for any comments.

[Qingguo]

Sorry  just one correction,   field name of container in index1 is "container" and is renamed as "id" in index2 event log. 

Index2 events (base query: index=index2 ...)
"id":"1497","start_time":"2021-09-26T8:53:16.232759Z"
"id":"1498","start_time":"2021-09-25T8:53:16.232759Z"

[somesoni2]

Try this:

(index=index1 playbook=100) OR (index=index2 ...)
| fields id container update_time start_time
| eval container=coalesce(id, container)
| stats values(start_time) as start_time values(update_time) as update_time by container
| eval time_diff=strptime(update_time,"%Y-%m-%dT%H:%M:%S.%6N%Z")-strptime(start_time,"%Y-%m-%dT%H:%M:%S.%6N%Z")

[Qingguo]

Just one clarification,  "id" was being used in index1 for other meaning.    

Testing your query , I cant get values of start_time in index2

[Qingguo]

Thanks @somesoni2.

Can we have a quick webex and discussion on this ?  I am on this webex in another 1 hour from now.

Join Mywebex

[somesoni2]

Try this

(index=index1 playbook=100) OR (index=index2 ...)
| fields container update_time start_time
| stats values(start_time) as start_time values(update_time) as update_time by container
| eval time_diff=strptime(update_time,"%Y-%m-%dT%H:%M:%S.%6N%Z")-strptime(start_time,"%Y-%m-%dT%H:%M:%S.%6N%Z")

[somesoni2]

Try something like this

(index=index_1 sourcetype=sourcetypeHere contidtion_1 ) OR (index=index_2 sourcetype=sourcetypeHere contidtion_2 )

| fields field_1 field_2 field_3

| stats values(field_2) as field_2 values(field_3) as field_3 by field_1

| eval diff=field_2-field_3

[PickleRick]

This question is way too generic. It depends. Often you can avoid subsearch alltogether with clever data manipulation. But sometimes you can't and you need simple subsearch or even | map

Hard to say without knowing more details.

[ITWhisperer]

Do you mean a join between index1 and index2 using field1?

search index_1 ...
| join field_1 [search index_2 ...]