How to pass event time from an inner subsearch to the outer search results table?

[Glasses]

Hi, 

Here are my searches

index=foo <search criteria> | table user _time
index=bar <search criteria> | table user _time

The user field values are passed from inner to outer search

index=foo [search index=bar <search criteria> | eval time1=_time | table user time1] <search criteria> |eval time2=_time| table user time1 time2

I want to create a table >>>> user  time1 time2 

Then I will be doing a delta on the time diff.

I am stuck trying to get the time carried over from the inner search to the outer search, not sure if this way is even possible...  its been a while but I am pretty sure I have done this before...

Seems like whenever I pass the new field time1, the outer search tries to search with that as criteria, which produces no results...

Thx!

[richgalloway · Accepted solution]

Run the subsearch by itself with the format command appended to see what it is passing to the main search.

index=bar <search criteria> 
| eval time1=_time 
| table user time1 
| format

You should get results that look a bit like this:

((user=foo time1=bar) OR (user=foo2 time1=bar2) OR (user=foo3 time1=bar3))

When that is added to the main search it looks like this:

index=foo ((user=foo time1=bar) OR (user=foo2 time1=bar2) OR (user=foo3 time1=bar3)) <search criteria> 
| eval time2=_time
| table user time1 time2

It should work well if the index in the main search has a field called "time"1", but otherwise you'll end up with nothing.

Use a subsearch when you need the results of a search to become part of the enclosing search.  If you just need to combine the results of two searches then there are easier ways.

index=foo <search criteria> 
| fields user _time
| eval time1=_time
| append [ search index=bar <search criteria> 
  | fields user _time
  | eval time2=_time
]
| stats values(*) as * by user
| table user time1 time2

[Glasses]

Hi Rich, 

The table format I want is 

user           time2         time1       match   diff

alice        1619615378	   1619615378	  yes     0

bob          1619534249    1619534249     yes     0

charlie      1619541847    1619541846     no      1

I know that is more than this thread covers but here is where I am stuck

index=foo <search criteria> 
| fields user _time
| eval time1=_time
| append [ search index=bar <search criteria> 
  | fields user _time
  | eval time2=_time ]
| chart values(time2) as time2 values(time1) as time1 over user

currently trying to get the "match" to work and find the "difference" between (time2-time1) but with chart "values" I have multiple lines per user, still trying to wrangle this....

for example, this is a result  

alice       1619534249   1619534249      
            1619614029   1619614029	      
            1619614364   1619614363       

[richgalloway]

Perhaps this will help get the remaining fields.

index=foo <search criteria> 
| fields user _time
| eval time1=_time
| append [ search index=bar <search criteria> 
  | fields user _time
  | eval time2=_time
]
| stats values(*) as * by user
| eval match=if(time1=time2, "yes", "no"), diff=time2-time1)
| table user time1 time2 match diff

[Glasses]

Thank you Rich.  I apologize the query requestors changed directions on what they need but yes, you replies have helped, especially explaining the " | append [subsearch] "...

[Glasses]

Thanks Rich your suggestion helps get me closer, only issue is the table does not list exactly the way I want, but I think I can solve that, if not I will ask another question.

[richgalloway]

I think I know why the table doesn't look right.  Try my revised answer.