Getting Data In

Joining two search based on closest time

eidil
Explorer

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?

 

0 Karma
1 Solution

thambisetty
SplunkTrust
SplunkTrust

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)

 

 

Annotation 2020-09-08 082005.png 

like answer if it solves your problem.

————————————
If this helps, give a like below.

View solution in original post

thambisetty
SplunkTrust
SplunkTrust

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

————————————
If this helps, give a like below.
0 Karma

eidil
Explorer

Somethg like this

0 Karma

eidil
Explorer

eidil_0-1599720109186.png

 

0 Karma

thambisetty
SplunkTrust
SplunkTrust

@eidil 

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

————————————
If this helps, give a like below.
0 Karma

thambisetty
SplunkTrust
SplunkTrust

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)

 

 

Annotation 2020-09-08 082005.png 

like answer if it solves your problem.

————————————
If this helps, give a like below.

eidil
Explorer

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?

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...