count occurrence of value in field in a single event

[navap123]

I have 6 sources with json event in the following structure (each source with different data of tests):

 "tests": [
              {
                "name": "test1",
                "status": "pass",
                "startAt": "",
                "finshedAt": "",
                "duration": ""
              },
              {
                "name": "test1",
                "status": "pass",
                "startAt": "",
                "finshedAt": "",
                "duration": ""
              },
              {
                "name": "test1",
                "status": "pass",
                "startAt": "",
                "finshedAt": "",
                "duration": ""
              }
            ]
          }

I need to count number of tests with status: pass and number of tests with status fail total for all events.
For example if I have 3 tests in each source I expect to get total of 18 tests in status pass.
When I use the following search:

index=aaf_jsonexecutionDetails.build="6.78.135"  | rename tests{}.status as status|stats 
     count(eval(if(status="pass", 1, null()))) as success_count
     count(eval(if(status="fail", 1, null()))) as failure_count

I get the number of events according to the search criteria (total 6) but not total for all sources tests{}.status = "pass"

Any Advice?

[to4kawa]

tricky:

index=aaf_jsonexecutionDetails.build="6.78.135" 
| rex mode=sed "s/pass/1/g s/fail/0/g"
| spath
| stats sum("tests{}.status") as success_count count("tests{}.status") as total_count
| eval failure_count=total_count - success_count

simple:

index=aaf_jsonexecutionDetails.build="6.78.135" 
| stats count by tests{}.status

As you like.

[navap123]

It doesn't solve the problem
I still get 6 results total while the problem is I get each result from each source while I need to sum total results from all sources,

[to4kawa]

| makeresults 
| eval _raw="raw\"
    {\"tests\":[{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"}]}
{\"tests\":[{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"}]}
{\"tests\":[{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"}]}
{\"tests\":[{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"}]}
{\"tests\":[{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"}]}
{\"tests\":[{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"},{\"name\":\"test1\",\"status\":\"pass\",\"startAt\":\"\",\"finshedAt\":\"\",\"duration\":\"\"}]}" 
| multikv forceheader=1 
| table _raw 
| rex mode=sed "s/pass/1/g s/fail/0/g" 
| spath 
| stats sum("tests{}.status") as success_count count("tests{}.status") as total_count 
| eval failure_count=total_count - success_count

tricky is not work. I'm sorry. you can fix it.