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.
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
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
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
This line: | stats values(combinedField) as combinedField by sharedField, Field2
should be | stats values(combinedField) as combinedField, values(State2) as State2 by sharedField, Field2