topic Re: Real-time inner join in Splunk Search

Real-time inner join

eroffol — Tue, 31 Oct 2017 14:46:09 GMT

I have data that looks like this:

I would like to join it in such a way to make it look like this:

This must work in real-time, so joins or subsearches are out the window. I have been messing around with stats, and eventstats, but can't seem to find a way to keep the values in the state field correctly.

Re: Real-time inner join

eroffol — Tue, 31 Oct 2017 16:50:08 GMT

I came up with a shady solution for this:
...
| eval combinedField = Field1 + "~" + State1
| eventstats values(combinedField) as combinedField by sharedField
| stats values(combinedField) as combinedField by sharedField, Field2
| mvexpand combinedField
| rex field=inputCombined "(?.)~(?.)"
| stats values(Field1) as Field1, values(State1) as State1, values(State2) as State2 by sharedField, Field2

Re: Real-time inner join

eroffol — Tue, 31 Oct 2017 16:52:09 GMT

This line: | stats values(combinedField) as combinedField by sharedField, Field2 should be | stats values(combinedField) as combinedField, values(State2) as State2 by sharedField, Field2

Re: Real-time inner join

eroffol — Tue, 31 Oct 2017 16:57:51 GMT

Revised solution since I can't edit my original answer:

...
| eval combinedField = Field1 + "~" + State1
| eventstats values(combinedField) as combinedField by sharedField
| stats values(combinedField) as combinedField, values(State2) as State2 by sharedField, Field2
| mvexpand combinedField
| rex field=inputCombined "(?<Field1>.*)~(?<State1>.*)"
| stats values(Field1) as Field1, values(State1) as State1, values(State2) as State2 by sharedField, Field2