topic Re: Joining two search based on closest time in Getting Data In

Joining two search based on closest time

eidil — Tue, 08 Sep 2020 02:48:25 GMT

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?

Re: Joining two search based on closest time

thambisetty — Thu, 10 Sep 2020 06:51:59 GMT

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.

Re: Joining two search based on closest time

eidil — Wed, 09 Sep 2020 08:28:27 GMT

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?

Re: Joining two search based on closest time

thambisetty — Wed, 09 Sep 2020 08:30:30 GMT

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

Re: Joining two search based on closest time

eidil — Thu, 10 Sep 2020 06:41:22 GMT

Somethg like this

Re: Joining two search based on closest time

eidil — Thu, 10 Sep 2020 06:41:58 GMT

Re: Joining two search based on closest time

thambisetty — Thu, 10 Sep 2020 06:52:33 GMT

@eidil

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