topic how to expand multi value fields with different values in column values in Splunk Search

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

Rajkumarkbm22 — Wed, 06 Jun 2018 17:39:16 GMT

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.

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

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

martin_mueller — Wed, 06 Jun 2018 22:16:36 GMT

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

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

Rajkumarkbm2 — Thu, 07 Jun 2018 07:16:05 GMT

See the below question: same scenario

https://answers.splunk.com/answers/663556/how-to-expand-multi-value-fields-with-different-fo.html?minQuestionBodyLength=80

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

DalJeanis — Thu, 07 Jun 2018 15:34:57 GMT

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)