Joining two search based on closest time

[eidil]

I am trying to join two searches based on closest time to match ticketnum with its real event e.g.

index=monitoring,

12:01:00 host=abc  status=down

3:05:00  host=abc status=down

index=ticket

12.03:00 host=abc  ticketnum=inc123

3:07:00 host=abc  ticketnum=inc456

Any idea on how to join these two based on closest time?

[thambisetty]

that you would know which fields are required in report. I provided solution based on your query. please provide details.

[eidil]

Somethg like this

[eidil]

[thambisetty]

@eidil 

check my updated answer : added values(*) as * to stats command

[thambisetty · Accepted solution]

try something like below

index=monitoring OR index=ticket
| stats values(*) as * values(status) as status values(ticketnum) as ticketnum by _time, host
| streamstats last(status) as last_status by host
| fields - status
| where isnotnull(ticketnum)

 

like answer if it solves your problem.

[eidil]

Hi @thambisetty , it seems d-able but the real logs are having much more details.  How do we know which fields needs to be written in the streamstats command?