Getting json argument value of an attribute depending on value of another attribute.

[dipudan]

Hi Everyone,
I am new with splunk queries. I am trying to retrieve a table with the data's build_number,errorstacktrace, and the testname for the nodes which is having "errorstacktrace" attribute in the below json. I tried the query

index="*" job_name="testcafe" "event_tag":"build_report" | table build_number ,testsuite.failures,testsuite.testcase{}.errorstacktrace,testsuite.testcase{}.testname | where 'testsuite.testcase{}.errorstacktrace' not null

but it is giving me all the testname in the json. I only wan't the "testname" which is having "errorstacktrace" attribute. Can anyone guide on how to proceed here ?

{
"event_tag":"build_report",
"metadata":{

},
"job_name":"TestCafe",
"testsuite":{
"failures":2,
"passes":7,
"skips":0,
"total":9,
"duration":115.06799,
"tests":9,
"time":115.06799,
"errors":0,
"testcase":[
{
"duration":1.664,
"classname":"API",
"testname":"should return last 10 posts",
"groupname":"TestCafe Tests_ Chrome 79.0.3945.88 _ Windows 10",
"skipped":false,
"failedsince":0,
"uniquename":"API.should return last 10 posts",
"status":"PASSED"
},
{
"duration":11.816,
"classname":"Login Test",
"testname":"should log into QualityShepherd.com",
"groupname":"TestCafe Tests_ Chrome 79.0.3945.88 _ Windows 10",
"skipped":false,
"failedsince":0,
"uniquename":"Login Test.should log into QualityShepherd.com",
"status":"PASSED"
},
{
"duration":2.424,
"classname":"Login Test",
"testname":"invalid credentials should display error",
"groupname":"TestCafe Tests_ Chrome 79.0.3945.88 _ Windows 10",
"skipped":false,
"failedsince":0,
"uniquename":"Login Test.invalid credentials should display error",
"status":"PASSED"
},
{
"duration":5.357,
"classname":"Quality Shepherd blog",
"testname":"should display 5 posts per page",
"groupname":"TestCafe Tests_ Chrome 79.0.3945.88 _ Windows 10",
"skipped":false,
"errorstacktrace":"\n \n 1) AssertionError: expected 5 to deeply equal",
"failedsince":29,
"uniquename":"Quality Shepherd blog.should display 5 posts per page",
"status":"FAILURE"
},
{
"duration":9.18,
"classname":"Quality Shepherd blog",
"testname":"should return search results",
"groupname":"TestCafe Tests_ Chrome 79.0.3945.88 _ Windows 10",
"skipped":false,
"failedsince":0,
"uniquename":"Quality Shepherd blog.should return search results",
"status":"PASSED"
},
{
"duration":6.499,
"classname":"Quality Shepherd blog",
"testname":"unfound search term should return no results",
"groupname":"TestCafe Tests_ Chrome 79.0.3945.88 _ Windows 10",
"skipped":false,
"failedsince":0,
"uniquename":"Quality Shepherd blog.unfound search term should return no results",
"status":"PASSED"
},
{
"duration":9.719,
"classname":"Quality Shepherd blog",
"testname":"should open social media link in new window",
"groupname":"TestCafe Tests_ Chrome 79.0.3945.88 _ Windows 10",
"skipped":false,
"failedsince":0,
"uniquename":"Quality Shepherd blog.should open social media link in new window",
"status":"PASSED"
},
{
"duration":5.123,
"classname":"Quality Shepherd blog",
"testname":"sidebar should have a set width",
"groupname":"TestCafe Tests_ Chrome 79.0.3945.88 _ Windows 10",
"skipped":false,
"failedsince":0,
"uniquename":"Quality Shepherd blog.sidebar should have a set width",
"status":"PASSED"
},
{
"duration":63.286,
"classname":"Quality Shepherd blog",
"testname":"should find an older post by paging",
"groupname":"TestCafe Tests_ Chrome 79.0.3945.88 _ Windows 10",
"skipped":false,
"errorstacktrace":"1) The specified selector does not match any element in the DOM tree.",
"failedsince":30,
"uniquename":"Quality Shepherd blog.should find an older post by paging",
"status":"FAILURE"
}
]
},
"build_number":30,
"page_num":1,
"job_result":"FAILURE",
"user":"Dipu Krishnan",
"build_url":"job/TestCafe/30/"
}

[niketn]

@dipudan I know you already have an answer that works for you. Here is a different approach where, errorstacktrace node is inserted to your raw data with dummy value ( NA in this case) for the SPL to perform 1 to 1 mapping for each fields in the test case being executed. The following should print out the complete report from where you can apply filter on errorstacktrace != NA to get specific rows.

<yourCurrentSearch>
| eval _raw=replace(_raw,"(\"skipped\":[^\,]+,\s+)(\"failedsince\")","\1\"errorstacktrace\":\"NA\",\2") 
| spath 
| fields - _* 
| rename "testsuite.testcase{}.testname" as testname, "testsuite.testcase{}.errorstacktrace" as errorstacktrace, "testsuite.testcase{}.duration" as duration, "testsuite.testcase{}.failedsince" as failedsince 
| fields build_number job_result testsuite.duration testsuite.failures testsuite.passes testsuite.total testsuite.duration testsuite.skips testname errorstacktrace duration failedsince
| eval data=mvzip(mvzip(mvzip(testname,errorstacktrace),duration),failedsince) 
| mvexpand data
| makemv data delim=","
| eval testname=mvindex(data,0),errorstacktrace=mvindex(data,1),duration=mvindex(data,2),failedsince=mvindex(data,3) 
| fields - data 
| eval errorstacktrace=replace(errorstacktrace,"\s+","")

[to4kawa · Accepted solution]

| makeresults
| eval _raw="{
\"event_tag\":\"build_report\",
\"metadata\":{

},
\"job_name\":\"TestCafe\",
\"testsuite\":{
\"failures\":2,
\"passes\":7,
\"skips\":0,
\"total\":9,
\"duration\":115.06799,
\"tests\":9,
\"time\":115.06799,
\"errors\":0,
\"testcase\":[
{
\"duration\":1.664,
\"classname\":\"API\",
\"testname\":\"should return last 10 posts\",
\"groupname\":\"TestCafe Tests_ Chrome 79.0.3945.88 Windows 10\",
\"skipped\":false,
\"failedsince\":0,
\"uniquename\":\"API.should return last 10 posts\",
\"status\":\"PASSED\"
},
{
\"duration\":11.816,
\"classname\":\"Login Test\",
\"testname\":\"should log into QualityShepherd.com\",
\"groupname\":\"TestCafe Tests Chrome 79.0.3945.88 Windows 10\",
\"skipped\":false,
\"failedsince\":0,
\"uniquename\":\"Login Test.should log into QualityShepherd.com\",
\"status\":\"PASSED\"
},
{
\"duration\":2.424,
\"classname\":\"Login Test\",
\"testname\":\"invalid credentials should display error\",
\"groupname\":\"TestCafe Tests Chrome 79.0.3945.88 Windows 10\",
\"skipped\":false,
\"failedsince\":0,
\"uniquename\":\"Login Test.invalid credentials should display error\",
\"status\":\"PASSED\"
},
{
\"duration\":5.357,
\"classname\":\"Quality Shepherd blog\",
\"testname\":\"should display 5 posts per page\",
\"groupname\":\"TestCafe Tests Chrome 79.0.3945.88 Windows 10\",
\"skipped\":false,
\"errorstacktrace\":\"\n \n 1) AssertionError: expected 5 to deeply equal\",
\"failedsince\":29,
\"uniquename\":\"Quality Shepherd blog.should display 5 posts per page\",
\"status\":\"FAILURE\"
},
{
\"duration\":9.18,
\"classname\":\"Quality Shepherd blog\",
\"testname\":\"should return search results\",
\"groupname\":\"TestCafe Tests Chrome 79.0.3945.88 Windows 10\",
\"skipped\":false,
\"failedsince\":0,
\"uniquename\":\"Quality Shepherd blog.should return search results\",
\"status\":\"PASSED\"
},
{
\"duration\":6.499,
\"classname\":\"Quality Shepherd blog\",
\"testname\":\"unfound search term should return no results\",
\"groupname\":\"TestCafe Tests Chrome 79.0.3945.88 Windows 10\",
\"skipped\":false,
\"failedsince\":0,
\"uniquename\":\"Quality Shepherd blog.unfound search term should return no results\",
\"status\":\"PASSED\"
},
{
\"duration\":9.719,
\"classname\":\"Quality Shepherd blog\",
\"testname\":\"should open social media link in new window\",
\"groupname\":\"TestCafe Tests Chrome 79.0.3945.88 Windows 10\",
\"skipped\":false,
\"failedsince\":0,
\"uniquename\":\"Quality Shepherd blog.should open social media link in new window\",
\"status\":\"PASSED\"
},
{
\"duration\":5.123,
\"classname\":\"Quality Shepherd blog\",
\"testname\":\"sidebar should have a set width\",
\"groupname\":\"TestCafe Tests Chrome 79.0.3945.88 Windows 10\",
\"skipped\":false,
\"failedsince\":0,
\"uniquename\":\"Quality Shepherd blog.sidebar should have a set width\",
\"status\":\"PASSED\"
},
{
\"duration\":63.286,
\"classname\":\"Quality Shepherd blog\",
\"testname\":\"should find an older post by paging\",
\"groupname\":\"TestCafe Tests Chrome 79.0.3945.88 _ Windows 10\",
\"skipped\":false,
\"errorstacktrace\":\"1) The specified selector does not match any element in the DOM tree.\",
\"failedsince\":30,
\"uniquename\":\"Quality Shepherd blog.should find an older post by paging\",
\"status\":\"FAILURE\"
}
]
},
\"build_number\":30,
\"page_num\":1,
\"job_result\":\"FAILURE\",
\"user\":\"Dipu Krishnan\",
\"build_url\":\"job/TestCafe/30/\"
}"
| rename COMMENT as "This is sample you provide"
| rename COMMENT as "From here, the logic"
| eval build_number=spath(_raw,"build_number")
| eval failures=spath(_raw,"testsuite.failures")
| spath path=testsuite.testcase{} output=testcase
| fields - _*
| mvexpand testcase
| where mvfind(testcase,"errorstacktrace") = 0
| spath input=testcase
| table build_number ,failures,errorstacktrace,testname

Hi, @dipudan
How about this?

[niketn]

Thanks @to4kawa, learnt a new way to use spath with eval and mvfind with where 🙂

[to4kawa]

Hooray XD　

[dipudan]

@to4kawa
It worked perfectly. Thank you.

[to4kawa]

your welcome