Splunk Search

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

Builder

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=jsondata
| spath output=WF
Label path=wf.steps{}.label
| spath output=WFStepStatusDate path=wf.steps{}.status{}.dates{}.ts.$date
| spath output=WF
StepDaysAllowed path=wf.steps{}.status{}.daysAllowed
| spath output=WFStepStatus path=wf.steps{}.status{}.dates{}.type
| spath output=WFName path=wf.label
| spath output=AssessmentName path=info.name
| table AssessmentName WF
Label WFName WFStepStatusDate WFStepDaysAllowed WFStep_Status

I get a table of all the fields
What I need is a rows of AssessmentName WFName with the columns WFLabel WFStepDaysAllowed WFStepStatusDate WFStepStatus

I attemped this but was unsuccessful obviously since you can't appendcols unless you use transform:
index=jsondata
| spath output=WF
Name path=wf.label
| spath output=AssessmentName path=info.name
| table AssessmentName WFName
| appendcols [search index=json
data
| spath output=WFLabel path=wf.steps{}.label
| spath output=WF
StepStatusDate path=wf.steps{}.status{}.dates{}.ts.$date
| spath output=WFStepDaysAllowed path=wf.steps{}.status{}.daysAllowed
| spath output=WF
StepStatus path=wf.steps{}.status{}.dates{}.type
| eval wf
process=mvzip(WFStepStatusDate,WFStepStatus)
| eval wf
process2=mvzip(wfprocess,WFStepDaysAllowed)
| eval wfprocess3=mvzip(wfprocess2,AssessmentName)
| eval wfprocess4=mvzip(wfprocess3,WFName)
| eval wf
process5=mvzip(wfprocess4,WFLabel)
| table WFLabel WFStepDaysAllowed WFStepStatusDate WFStep_Status]

Any ideas?
Thanks a bunch!

0 Karma

Contributor

Hi @tkwaller,
Can you try stats instead of table?
base search | stats values(WFLabel) values(WFStepDaysAllowed) values(WFStepStatusDate) values(WFStepStatus) BY AssessmentName, WFName

0 Karma

Communicator

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(WFStepStatus). Also there ARE instances where values(WF_Step_Status) has a start but not a complete but I dont see that record in results.

0 Karma

SplunkTrust
SplunkTrust

@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.

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

Builder

Hello
Yes some of the fields are multivalued, WFName WFStepStatusDate WFStepDaysAllowed WFStep_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.

0 Karma