Getting Data In

How can I get my mvexpand to work

tkwaller_2
Communicator

Hello

I have a search I am having an issue with, I am trying to get the JSON array data in a table, efficiently.
My search that works is:
index=json_data
| spath output=WF_Label path=wf.steps{}.label
| spath output=WF_Step_Status_Date path=wf.steps{}.status{}.dates{}.ts.$date
| spath output=WF_Step_Days_Allowed path=wf.steps{}.status{}.daysAllowed
| spath output=WF_Step_Status path=wf.steps{}.status{}.dates{}.type
| spath output=WF_Name path=wf.label
| spath output=AssessmentName path=info.name
| table AssessmentName WF_Label WF_Name WF_Step_Status_Date WF_Step_Days_Allowed WF_Step_Status

What I am trying to do is eval the fields and mvzip the data, mvexpand that and then table it.
I tried:
index=json_data
| spath output=WF_Label path=wf.steps{}.label
| spath output=WF_Step_Status_Date path=wf.steps{}.status{}.dates{}.ts.$date
| spath output=WF_Step_Days_Allowed path=wf.steps{}.status{}.daysAllowed
| spath output=WF_Step_Status path=wf.steps{}.status{}.dates{}.type
| spath output=WF_Name path=wf.label
| spath output=AssessmentName path=info.name
| eval wf_process=mvzip(WF_Label,mvzip(WF_Name,mvzip(AssessmentName,mvzip(WF_Step_Days_allowed,mvzip(WF_Step_Status_Date,WF_Step_Status)))))
| mvexpand wf_process
| table AssessmentName WF_Label WF_Name WF_Step_Status_Date WF_Step_Days_Allowed WF_Step_Status

This search completes BUT with the message:
"Field 'wf_process' does not exist in the data." So its not actually working. I am trying to get the data in the arrays expanded without using mvexpand but the one time, as it is expensive search time wise.

Thanks for any assistance!

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

That message indicates that the field wf_process was not created, which implies that one or more of the fields in the prior eval that was supposed to create is is either null, or misspelled. Put this in the place of the mvzips, and see what you get...

| eval count1=coalesce(mvcount(WF_Label),0)
| eval count2=coalesce(mvcount(WF_Name),0)
| eval count3=coalesce(mvcount(AssessmentName),0)
| eval count4=coalesce(mvcount(WF_Step_Days_allowed),0)
| eval count5=coalesce(mvcount(WF_Step_Status_Date),0)
| eval count6=coalesce(mvcount(WF_Step_Status),0)
| eval match12=case(count1!=count2,1)
| eval match13=case(count1!=count3,1)
| eval match14=case(count1!=count4,1)
| eval match15=case(count1!=count5,1)
| eval match16=case(count1!=count6,1)
| eval match99=case(count1*count2*count3*count4*count5*count6=0,1)
| where isnotnull(match12) OR isnotnull(match13) OR isnotnull(match14) OR isnotnull(match15) OR isnotnull(match16) OR isnotnull(match99)
| head 5

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

That message indicates that the field wf_process was not created, which implies that one or more of the fields in the prior eval that was supposed to create is is either null, or misspelled. Put this in the place of the mvzips, and see what you get...

| eval count1=coalesce(mvcount(WF_Label),0)
| eval count2=coalesce(mvcount(WF_Name),0)
| eval count3=coalesce(mvcount(AssessmentName),0)
| eval count4=coalesce(mvcount(WF_Step_Days_allowed),0)
| eval count5=coalesce(mvcount(WF_Step_Status_Date),0)
| eval count6=coalesce(mvcount(WF_Step_Status),0)
| eval match12=case(count1!=count2,1)
| eval match13=case(count1!=count3,1)
| eval match14=case(count1!=count4,1)
| eval match15=case(count1!=count5,1)
| eval match16=case(count1!=count6,1)
| eval match99=case(count1*count2*count3*count4*count5*count6=0,1)
| where isnotnull(match12) OR isnotnull(match13) OR isnotnull(match14) OR isnotnull(match15) OR isnotnull(match16) OR isnotnull(match99)
| head 5

View solution in original post

tkwaller_2
Communicator

So I did this, and it works without errors BUT it doesnt seem to expand or it looks like its not expanding as the fields with multiple values are not broken out into individual lines

index=json_data
| spath output=WF_Label path=wf.steps{}.label
| spath output=WF_Step_Status_Date path=wf.steps{}.status{}.dates{}.ts.$date
| spath output=WF_Step_Days_Allowed  path=wf.steps{}.status{}.daysAllowed
| spath output=WF_Step_Status path=wf.steps{}.status{}.dates{}.type
| spath output=WF_Name path=wf.label
| spath output=AssessmentName path=info.name
| eval wf_process=mvzip(WF_Step_Status_Date,WF_Step_Status,",")
| eval wf_process2=mvzip(wf_process,WF_Step_Days_Allowed,",")
| eval wf_process3=mvzip(wf_process2,AssessmentName,",")
| eval wf_process4=mvzip(wf_process3,WF_Name,",")
| eval wf_process5=mvzip(wf_process4,WF_Label,",")
| mvexpand wf_process5
| table AssessmentName WF_Name WF_Label WF_Step_Days_Allowed WF_Step_Status_Date WF_Step_Status

What I would like the table to show is a row of AssessmentName , WF_Name with columns of WF_Label, WF_Step_Days_Allowed, WF_Step_Status_Date, WF_Step_Status

Any ideas?
Thanks!

0 Karma

tkwaller_2
Communicator

UPDATE:
I got this but I need to have 1 row for each WF_Label(New,InProgress,Completed) that includes the WF_Step_Status_Date within each WF_Label

index=json_data
| spath output=WF_Label path=wf.steps{}.label
| spath output=WF_Step_Status_Date path=wf.steps{}.status{}.dates{}.ts.$date
| spath output=WF_Step_Days_Allowed path=wf.steps{}.status{}.daysAllowed
| spath output=WF_Step_Status path=wf.steps{}.status{}.dates{}.type
| spath output=WF_Name path=wf.label
| spath output=AssessmentName path=info.name
| eval wf_process=mvzip(WF_Step_Status_Date,WF_Step_Status)
| eval wf_process2=mvzip(wf_process,WF_Step_Days_Allowed)
| eval wf_process3=mvzip(wf_process2,AssessmentName)
| eval wf_process4=mvzip(wf_process3,WF_Name)
| eval wf_process5=mvzip(wf_process4,WF_Label)
| table AssessmentName WF_Name WF_Label WF_Step_Days_Allowed WF_Step_Status_Date WF_Step_Status

0 Karma

niketnilay
Legend

@tkwaller_2, please add sample data for the above query so that community can assist you better.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

tkwaller_2
Communicator

I cant upload files due to karma points. This account has not been open long enough

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!