How can I fix the columns and rows in my table?

[tkwaller]

Hello

I get a table of all the fields from this search.
What I need is a rows of AssessmentName, WF_Name with the columns WF_Label, WF_Step_Days_Allowed, WF_Step_Status_Date, WF_Step_Status

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

I get a table of all the fields
What I need is a rows of AssessmentName WF_Name with the columns WF_Label WF_Step_Days_Allowed WF_Step_Status_Date WF_Step_Status

I attemped this but was unsuccessful obviously since you can't appendcols unless you use transform:
index=json_data
| spath output=WF_Name path=wf.label
| spath output=AssessmentName path=info.name
| table AssessmentName WF_Name
| appendcols [search 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
| 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 WF_Label WF_Step_Days_Allowed WF_Step_Status_Date WF_Step_Status]

Any ideas?
Thanks a bunch!

[nikita_p]

Hi @tkwaller,
Can you try stats instead of table?
base search | stats values(WF_Label) values(WF_Step_Days_Allowed) values(WF_Step_Status_Date) values(WF_Step_Status) BY AssessmentName, WF_Name

[tkwaller_2]

So this is very close to exactly right. I used:

| stats values(WF_Step_Status) BY AssessmentName, WF_Name, WF_Process, WF_Step, WF_Step_Status_Date, WF_Step_Days_Allowed

I get rows of these:

AssessmentName  WF_Name WF_Process  WF_Step WF_Step_Status_Date WF_Step_Days_Allowed    values(WF_Step_Status)
Test - Assessment 2 General Workflow    Completed   Submitted   2017-12-22T03:56:30.758+0000    5   
complete
start

I would really like a row for start and a row for complete, the values in the field values(WF_Step_Status). Also there ARE instances where values(WF_Step_Status) has a start but not a complete but I dont see that record in results.

[niketn]

@tkwaller, can you add sample data for your question? Also what is the output of the following? Are there any multi-valued fields or are these single value?

| table AssessmentName WF_Label WF_Name WF_Step_Status_Date WF_Step_Days_Allowed WF_Step_Status

Use the code button (101010) to post SPL and Data so that special characters do not escape.

[tkwaller]

Hello
Yes some of the fields are multivalued, WF_Name WF_Step_Status_Date WF_Step_Days_Allowed WF_Step_Status

I cant add data examples as the data is too large to paste here and the file attach wont add the file types, .txt .json etc.