Splunk Search

## Real-time inner join

Path Finder

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.

Tags (3)
1 Solution
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
``````
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
``````
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

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`

Get Updates on the Splunk Community!

#### Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...

#### Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

#### NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...