Splunk Search

Joining 2 Multivalue fields to generate new field value combinations

ltrand
Contributor

I'm working with some json data that contains 1 field with a list of keys and 1 field with a list of values. These pairs may change event to event, but item 1 in field 1 will always align with item 1 in field 2. So I'd like to join these together so that I get a field name of field1_value1 with the data of field2_value1.

A sample of where I am right now in this:

| makeresults count=1
| eval event.key="email,user,event_id,state"
| eval event.values="user@acme.corp,Jon Smith,1234,Open"
| makemv delim="," event.key
| makemv delim="," event.values
|eval keyjoin=mvzip('event.key','event.values')
| mvexpand keyjoin

So this will properly join the data into the field keyjoin, but now I have to take out the first value in it to be the field name and the second to be the field value. Any advice?

Edit:
The desired end state would be the ability to add further search criteria after formatting the data. This is going to drive several panels, so obviously more than that, but if I can get to that stats, then I can go from there. Just need to solve for MISSING SPL HERE

| makeresults count=1
| eval event.key="email,user,event_id,state"
| eval event.values="user@acme.corp,Jon Smith,1234,Open"
| makemv delim="," event.key
| makemv delim="," event.values
|eval keyjoin=mvzip('event.key','event.values')
| mvexpand keyjoin
| **MISSING SPL HERE**
| stats count by state, user
0 Karma
1 Solution

ltrand
Contributor

I finally figured out the solution here:

| makeresults count=1
 | eval event.key="email,user,event_id,state"
 | eval event.values="user@acme.corp,Jon Smith,1234,Open"
 | makemv delim="," event.key
 | makemv delim="," event.values
 |eval keyjoin=mvzip('event.key','event.values')
 | mvexpand keyjoin
 | rex field=keyjoin "^(?<key>[^,]+),(?<value>.*)$"
 | eval {key} = value
 | stats count by state

View solution in original post

ltrand
Contributor

I finally figured out the solution here:

| makeresults count=1
 | eval event.key="email,user,event_id,state"
 | eval event.values="user@acme.corp,Jon Smith,1234,Open"
 | makemv delim="," event.key
 | makemv delim="," event.values
 |eval keyjoin=mvzip('event.key','event.values')
 | mvexpand keyjoin
 | rex field=keyjoin "^(?<key>[^,]+),(?<value>.*)$"
 | eval {key} = value
 | stats count by state

sjbriggs
Path Finder

This helped me combine the values of two multi-valued fields which was helpful.  I'm just trying to figure out how to combine 3 values now.  Basically one mvfield has attributes of things changed in a user account.  The second field has the old value of the attribute that's been changed, while the 3rd field has the new value that the attribute has been changed to. 
attributes=group,role
oldvalue=user,admin
newvalue=superuser,null

The 3 fields don't consistently have the same count of attributes so the dynamic method recommended certainly helped.  I'm just struggling to reverse engineer it to handle 3 multivalue fields.  Any suggestions?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

There is an example of this in the Docs. See Example 3 under mvexpand in the Search Reference manual (https://docs.splunk.com/Documentation/Splunk/8.0.3/SearchReference/Mvexpand#Examples).

---
If this reply helps you, Karma would be appreciated.
0 Karma

ltrand
Contributor

Sorry if I wasn't clear. Ultimately I would like to be able to do further conditionals in the search, so I need to be able to do something like | stats count by state. So state must be a fieldname with the value Open.

0 Karma
Get Updates on the Splunk Community!

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...