topic Re: join two events with a common field in Splunk Search

join two events with a common field

pankajad — Tue, 19 Dec 2017 21:32:45 GMT

I want to join the below two events based on tid. For "Event1", there could be multiple" Event2"

Event1:
20171219.114132 myapp error statusCode=500 tid=14ec038e-ba3d-423d-836a-1c7b8fa3073d

Event2:
20171219.114132 Fn=makeRequest HttpStatusCode=401 ElapsedTime=79 tid=14ec038e-ba3d-423d-836a-1c7b8fa3073d
20171219.114132 Fn=makeRequest HttpStatusCode=200 ElapsedTime=50 tid=14ec038e-ba3d-423d-836a-1c7b8fa3073d

I have tried this so far but it doesn't give all the events -

index=myindex "myapp error"  |join tid [search  index=myindex Fn=makeRequest |fields tid, HttpStatusCode] |table tid, statusCode, HttpStatusCode

Re: join two events with a common field

nickhills — Tue, 19 Dec 2017 22:01:02 GMT

would transaction not be of use here?

index=myindex ("myapp error" OR Fn=makeRequest)  |transaction tid startswith="myapp error"|table tid, statusCode, HttpStatusCode

Re: join two events with a common field

pankajad — Wed, 20 Dec 2017 00:30:05 GMT

This didn't work. I'm getting all events from "Fn=makeRequest "and "myapp error". It's not doing any join

Re: join two events with a common field

kamlesh_vaghela — Wed, 20 Dec 2017 04:03:39 GMT

Hi @pankajad,

Can you please try search?

index=myindex "myapp error" 
| append 
    [ search index=myindex Fn=makeRequest 
    | fields tid, HttpStatusCode] 
| stats values(statusCode) as statusCode values(HttpStatusCode) as HttpStatusCode by tid

Check my sample search.

| makeresults 
| eval _raw="20171219.114132 myapp error statusCode=500 tid=14ec038e-ba3d-423d-836a-1c7b8fa3073d" 
| append 
    [| makeresults 
    | eval _raw="20171219.114132 Fn=makeRequest HttpStatusCode=401 ElapsedTime=79 tid=14ec038e-ba3d-423d-836a-1c7b8fa3073d"] 
| append 
    [| makeresults 
    | eval _raw="20171219.114132 Fn=makeRequest HttpStatusCode=200 ElapsedTime=50 tid=14ec038e-ba3d-423d-836a-1c7b8fa3073d"] 
| kv
| stats values(statusCode) as statusCode values(HttpStatusCode) as HttpStatusCode by tid

thanks

Re: join two events with a common field

nickhills — Wed, 20 Dec 2017 06:49:14 GMT

Transaction won’t join per se, it groups events based on a common field. tid in this case.

I have double checked my spl and I can’t see anything wrong, so am intrigued what it actually outputs for you.

Re: join two events with a common field

nickhills — Thu, 21 Dec 2017 09:46:30 GMT

I made a slight change to the suggestion above, could you try it again?