How to calculate the count of a field for multivalued data?

[anooshac]

I have JSON data which are multivalued. I want to create a overview table of the counts.

{
    "suite": [
        {
            "hostname": "localhost",
            "failures": 0,
            "package": "ABC",
            "tests": 0,
            "name": "ABC_test",
            "id": 0,
            "time": 0,
            "errors": 0,
            "testcase": [
                {
                    "classname": "xyz",
                    "name": "foo1",
                    "time": 0,
                    "status": "Passed"
                },
                {
                    "classname": "pqr",
                    "name": "foo2",
                    "time": 0,
                    "status": "Passed"
                },
               .
               .
               .
            ]
        }
    ]
}

This is the data. For a given project there'll be many JSON files like above. So i want to get the unique data while taking the counts. Tried with mvdedup, it did not work.

|spath output=jenkins_url path=JenkinsMetaData.JENKINS_URL
| spath output=suite path=suite{}.name 
| spath output=case path=suite{}.case{}.name 
| spath output=Build_Num path=JenkinsMetaData.buildnumber 
| spath output=Status path=suite{}.case{}.status 
| fields - _raw
| eventstats max(Build_Num) as Latest_Build by Job_Name 
| where Latest_Build=Build_Num


| stats values(Build_Num) as Build_Num 
  count(eval(Status="Execution Failed" OR Status="Testcase_Failed"))
  AS Failed_cases, 
 count(eval(Status="Passed")) AS Passed_cases, 
  count(eval(Status="Failed" OR Status="Testcase_Error")) AS Execution_Failed_cases, 
  dc(case) as Total_cases 
  dc(suite) as "Total suite" 
  by Job_Name Build_Variant Jenkins_Server 

When i do this Total_cases and Total suite are are correct, but other values are not correct.

But when i use |Status="Passed"| stats dc(case) as Passed_cases for one project, im getting correct value.

But my requirement is to create a table for all the projects.

Anyone know how to handle this?

[ITWhisperer]

Can you give an example of an event which generates the incorrect values? (Perhaps with the correct field names too?)

[anooshac]

Hi, @ITWhisperer 

The events are like below.

{
    "MetaData": {
        "JENKINS_URL": "https://abc.com",
        "stagename": "ABC_CT",
        "variantname": "NEW_ABC",
        "jobname": "abc",
        "buildnumber": 29,
        "filename": "1729005933566.json"
    },
    "suite": {
        "hostname": "localhost",
        "failures": 0,
        "package": "ABC",
        "tests": 0,
        "name": "ABC_test",
        "id": 0,
        "time": 0,
        "errors": 0,
        "case": [
            {
                "classname": "xyz",
                "name": "foo1",
                "time": 0,
                "status": "Passed"
            },
            {
                "classname": "pqr",
                "name": "foo2",
                "time": 0,
                "status": "Passed"
            },
........
        ]
    }
}

there will be so many events like this for a single project and the values will be repeated in those events. Like suite, case will be repeated.

index=... sourcetype=...
|spath ...
| stats 
  count(eval(Status="Execution Failed" OR Status="case_Failed"))
  AS Failed_cases, 
 count(eval(Status="Passed")) AS Passed_cases, 
  count(eval(Status="Failed" OR Status="case_Error")) AS Execution_Failed_cases, 
  dc(case) as Total_cases 
  dc(suite) as "Total suite" 
  by Job_Name Build_Variant Jenkins_Server 

I use spath to get every parameter, then i use them in the query.

[ITWhisperer]

The stats command is counting events not occurrences of status values. You need to use mvexpand to separate out the test cases so you can count the individual status values.

| spath suite.case{} output=cases
| mvexpand cases
| spath input=cases status output=Status
| spath input=cases name output=case
| spath suite.name output=suite
| spath MetaData.jobname output=Job_Name
| spath MetaData.buildnumber output=Build_Variant
| spath MetaData.JENKINS_URL output=Jenkins_Server
| stats 
  count(eval(Status="Execution Failed" OR Status="case_Failed"))
  AS Failed_cases, 
 count(eval(Status="Passed")) AS Passed_cases, 
  count(eval(Status="Failed" OR Status="case_Error")) AS Execution_Failed_cases, 
  dc(case) as Total_cases 
  dc(suite) as "Total suite" 
  by Job_Name Build_Variant Jenkins_Server