Splunk Search

Real-time inner join

eroffol
Path Finder

I have data that looks like this:
alt text

I would like to join it in such a way to make it look like this:
alt text
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.

0 Karma
1 Solution

eroffol
Path Finder

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

View solution in original post

0 Karma

eroffol
Path Finder

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

View solution in original post

0 Karma

eroffol
Path Finder

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

0 Karma

eroffol
Path Finder

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

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!