Splunk Search
Highlighted

how to expand multi value fields with different values in column values

New Member

Dear Experts,
Please provide a valuable solution for my problem.

I am having the fields from JSON which is having multivalue fields as below. In below example Department field having three values and Projects field having 5 values. I want expand this.

Name | EMP NO | Department | projects
ABCS | 1234567 | CS12345678 | PROJ1
| | AB12345678 | PROJ2
| | AB55555555 | PROJ3
| | | PROJ4
| | | PROJ5

I need output like the below
Name | EMP NO | Department | projects
ABCS | 1234567 | CS12345678 | PROJ1
ABCS | 1234567 | AB12345678 | PROJ2
ABCS | 1234567| AB55555555 | PROJ3
ABCS | 1234567 | NULL | PROJ4
ABCS | 1234567 | NULL | PROJ5

0 Karma
Highlighted

Re: how to expand multi value fields with different values in column values

SplunkTrust
SplunkTrust

Do post your json - it might be possible to extract the values correctly right away.

0 Karma
Highlighted

Re: how to expand multi value fields with different values in column values

Explorer
0 Karma
Highlighted

Re: how to expand multi value fields with different values in column values

SplunkTrust
SplunkTrust

I start with the assumption that you have a single record that has Name="ABCS", EmpNo="1234567", Department= a multivalue field with three values, Projects= a multivalue field with 5 values.

your search here 
 | table Name EmpNo Department Projects
 | streamstats count as recNo 
 | eval numRecs=If(mvcount(Department)>mvcount(Projects),mvcount(Department),mvcount(Projects)
 | eval myFan=mvrange(0,numRecs)
 | mvexpand myFan
 | eval Department=case(myFan<mvcount(Department),mvindex(Department,myFan), 
     true(),"NULL")
 | eval Projects=case(myFan<mvcount(Department),mvindex(Department,myFan), 
     true(),"NULL")

Now you have five separate records as requested.

The recNo field is a record number in case you ever want to put them back together again. You can also use it (if desired) for break logic. That could look something like this...

your search here 
| table Name EmpNo Department Projects
| streamstats count as recNo 
| eval numRecs=If(mvcount(Department)>mvcount(Projects),mvcount(Department),mvcount(Projects)
| eval nextRec=numRecs+1
| eval myFan=mvrange(0,nextRec)
| mvexpand myFan
| eval Department=case(myFan<mvcount(Department),mvindex(Department,myFan),
    myFan=numRecs,"",
    true(),"NULL")
| eval Projects=case(myFan<mvcount(Department),mvindex(Department,myFan), 
    myFan=numRecs,"", 
    true(),"NULL")
| eval Name=case(myFan=numRecs,"",
    true(),Name)
| eval EmpNo=case(myFan=numRecs,"",
    true(),EmpNo)