Splunk Search

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

Rajkumarkbm22
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

DalJeanis
Legend

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)

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

Rajkumarkbm2
Explorer
0 Karma
Get Updates on the Splunk Community!

Good Sourcetype Naming

When it comes to getting data in, one of the earliest decisions made is what to use as a sourcetype. Often, ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Splunk App for Anomaly Detection End of Life Announcement

Q: What is happening to the Splunk App for Anomaly Detection?A: Splunk is officially announcing the ...