Excluding field values from JSON SPATH Table

mishutts — Fri, 30 Oct 2020 19:59:37 GMT

Hi,

Can someone help filter out a nested JSON value in a table?

I have a search and SPATH command where I can't figure out how exclude stage{}.status=SUCCESS so that only failures are shown in the table. Adding stage{}.status!=SUCCESS doesn't work due to multiple nested JSON fields containing both SUCCESS and FAILURES.

Here is the search I'm using:

index="jenkins_statistics" event_tag=job_event type="completed" stages{}.status=FAILURE | spath stages{} output=Stages | table job_name job_result stages{}.name stages{}.status stages{}.error stages{}.error_type

Thank you

Here is how the table looks:

Here is the raw event:

Here is a highlighted Example:

10/29/20
11:26:02.217 AM
{ [-]
build_number: 1234
build_url: job/SomeSite/job/SomeJob_Build/1234/
event_tag: job_event
job_duration: 261.361
job_name: SomeSite/SomeJob_Build
job_result: FAILURE
job_started_at: 2020-10-29T15:21:40Z
job_type: Pipeline
label: nojobs
metadata: { [-]
BITBUCKET_PR_ID:
BRANCH_SOURCE:
BRANCH_TO_BUILD:
scm: git
}
node: (master)
queue_id: 123456
queue_time: 9.549
scm: git
stages: [ [-]
{ [-]
children: [ [+]
]
duration: 59.661
id: 10
name: CheckoutLogic
pause_duration: 0
start_time: 1603984902
status: SUCCESS
}
{ [-]
children: [ [+]
]
duration: 112.969
id: 37
name: Install Dependancies
pause_duration: 0
start_time: 1603984961
status: SUCCESS
}
{ [-]
duration: 0.021
id: 42
name: Test
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
duration: 0.016
id: 45
name: Branch: Lint, Unit Test
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
children: [ [+]
]
duration: 47.47
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 48
name: Lint, Unit Test
pause_duration: 0
start_time: 1603985074
status: FAILURE
}
{ [-]
duration: 0.019
id: 46
name: Branch: Some Analysis
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
duration: 0.054
id: 50
name: Some Analysis
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
children: [ [+]
]
duration: 23.417
id: 57
name: Building Dev_Site » Some_scan
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
duration: 0.137
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 85
name: Build Code
pause_duration: 0
start_time: 1603985122
status: FAILURE
}
{ [-]
duration: 0.15
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 89
name: Package
pause_duration: 0
start_time: 1603985122
status: FAILURE
}
{ [-]
duration: 0.156
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 93
name: Zip Test Data
pause_duration: 0
start_time: 1603985122
status: FAILURE
}
{ [-]
duration: 0.218
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 97
name: Prepare Artifacts
pause_duration: 0
start_time: 1603985122
status: FAILURE
}
{ [-]
children: [ [+]
]
duration: 38.86
id: 101
name: Declarative: Post Actions
pause_duration: 0
start_time: 1603985123
status: SUCCESS
}
]
test_summary: { [+]
}
trigger_by: Started by user John Doe: Bitbucket PPR: pull request updated
type: completed
upstream:
user: (scm)
} 10/29/20
11:26:02.217 AM
{ [-]
build_number: 2999
build_url: job/Dev_Site/job/SomeSite_Build/2999/
event_tag: job_event
job_duration: 261.361
job_name: Dev_Site/SomeSite_Build
job_result: FAILURE
job_started_at: 2020-10-29T15:21:40Z
job_type: Pipeline
label: nojobs
metadata: { [-]
BITBUCKET_PR_ID:
BRANCH_SOURCE:
BRANCH_TO_BUILD:
scm: git
}
node: (master)
queue_id: 123456
queue_time: 9.549
scm: git
stages: [ [-]
{ [-]
children: [ [+]
]
duration: 59.661
id: 10
name: CheckoutLogic
pause_duration: 0
start_time: 1603984902
status: SUCCESS
}
{ [-]
children: [ [+]
]
duration: 112.969
id: 37
name: Install Dependancies
pause_duration: 0
start_time: 1603984961
status: SUCCESS
}
{ [-]
duration: 0.021
id: 42
name: Test
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
duration: 0.016
id: 45
name: Branch: Lint, Unit Test
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
children: [ [+]
]
duration: 47.47
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 48
name: Lint, Unit Test
pause_duration: 0
start_time: 1603985074
status: FAILURE
}
{ [-]
duration: 0.019
id: 46
name: Branch: Sonar Analysis
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
duration: 0.054
id: 50
name: Sonar Analysis
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
children: [ [+]
]
duration: 23.417
id: 57
name: Building Dev_Site » Some_scan
pause_duration: 0
start_time: 1603985074
status: SUCCESS
}
{ [-]
duration: 0.137
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 85
name: Build Code
pause_duration: 0
start_time: 1603985122
status: FAILURE
}
{ [-]
duration: 0.15
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 89
name: Package
pause_duration: 0
start_time: 1603985122
status: FAILURE
}
{ [-]
duration: 0.156
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 93
name: Zip Test Data
pause_duration: 0
start_time: 1603985122
status: FAILURE
}
{ [-]
duration: 0.218
error: MicrosoftAzureStorage - Error occurred while uploading to Azure - SomeDevSite
error_type: java.io.IOException
id: 97
name: Prepare Artifacts
pause_duration: 0
start_time: 1603985122
status: FAILURE
}
{ [-]
children: [ [+]
]
duration: 38.86
id: 101
name: Declarative: Post Actions
pause_duration: 0
start_time: 1603985123
status: SUCCESS
}
]
test_summary: { [+]
}
trigger_by: Started by user John Doe: Bitbucket PPR: pull request updated
type: completed
upstream:
user: (scm)
}

Re: Excluding field values from JSON SPATH Table

ITWhisperer — Fri, 30 Oct 2020 23:15:05 GMT

| eval name_status=mvzip('stages{}.name','stages{}.status') | eval name_status=mvfilter(like(name_status,"%FAILURE")) | eval stages_name = mvmap(name_status,mvindex(split(name_status,","),0)) | eval stages_status = mvmap(name_status,mvindex(split(name_status,","),1)) | table job_name job_result stages_name stages_status stages{}.error stages{}.error_type

Re: Excluding field values from JSON SPATH Table

mishutts — Mon, 02 Nov 2020 16:44:36 GMT

Thank you for the suggestion. When I add this to the end of my search, I get "Error in 'eval' command: The 'mvmap' function is unsupported or undefined. " Am I correct that the query should be without the spath command?I tried both way but same result.

"index="jenkins_statistics" event_tag=job_event type="completed" stages{}.status=FAILURE

| eval name_status=mvzip('stages{}.name','stages{}.status')
| eval name_status=mvfilter(like(name_status,"%FAILURE"))
| eval stages_name = mvmap(name_status,mvindex(split(name_status,","),0))
| eval stages_status = mvmap(name_status,mvindex(split(name_status,","),1))
| table job_name job_result stages_name stages_status stages{}.error stages{}.error_type"

Re: Excluding field values from JSON SPATH Table

ITWhisperer — Mon, 02 Nov 2020 17:11:48 GMT

You need at least 8.0 for the mvmap function. The spath function is required to extract the json fields.

Re: Excluding field values from JSON SPATH Table

mishutts — Wed, 04 Nov 2020 14:05:23 GMT

Thanks for the help. I can't test until we upgrade to 8 this month but believe the answer to be correct and will mark it solved.

Re: Excluding field values from JSON SPATH Table

mishutts — Wed, 09 Dec 2020 14:54:23 GMT

Now that I have our Splunk instance upgraded to 8, I have verified this solution is correct. Exactly what I needed. Thank you ITWhisperer!

topic Re: Excluding field values from JSON SPATH Table in Getting Data In

Excluding field values from JSON SPATH Table

Re: Excluding field values from JSON SPATH Table

Re: Excluding field values from JSON SPATH Table

Re: Excluding field values from JSON SPATH Table

Re: Excluding field values from JSON SPATH Table

Re: Excluding field values from JSON SPATH Table