Create a table with Json data

[anooshac]

I have a JSON data like this.

"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"},....

I want to create a table with Suite testcase_name and Testcase_status as columns. I have a solution using mvexpand command. But when there is large data output gets truncated using mvexpand command.

  ....| spath output=suite path=suite{}.name | spath output=Testcase path=suite{}.testcase{}.name| spath output=Error path=suite{}.testcase{}.error | spath output=Status path=suite{}.testcase{}.status|search (suite="*") 
| eval x=mvzip(Testcase,Status)
| mvexpand x|eval y=split(x,",")|eval Testcase=mvindex(y,0) 
| search Testcase IN ("***")
| eval suite=mvdedup(suite)
|eval Status=mvindex(y,1) |table "Suite" "TestCase" Status

This is the query im using. But the results gets truncated. Is there any alternative for mvexpand so that i can edit the above query ?

[PickleRick]

Splunk functions should _not_ truncate any data on their own (unless you explicitly use some text-manipulation function of course). There might be some visualization issue on the displaying end.

Anyway, You're doing one thing which in case of your data might be giving proper results but in general is a bad practice.

If you have multivalued fields (like your two Testcase and Status fields) you have no guarantee that they will contain entries matching 1-1 with each other.

A simple run-anywhere example to demonstrate:

| makeresults
| eval _raw="[ { \"a\":\"a\",\"b\":\"b\"},{\"a\":\"b\",\"c\":\"c\"},{\"b\":\"d\",\"c\":\"e\"}]"
| spath {}.a output=a
| spath {}.b output=b
| spath {}.c output=c
| spath {} output=pairs

As you can see, the output in fields a, b and c would be completely different if zipped together than what you get as pairs in the array.

That's why you should rather parse out whole separate testcases as json objects with

| spath testcase

(or whatever path you have there to your test cases)

and then parse each of them separately so you don't loose the connection between separate fields within a single testcase.

[anooshac · Accepted solution]

Hi, @PickleRick ,

    | spath output=suite path=suite{}.name | spath output=Testcase path=suite{}.testcase{}.name | spath output=Status path=suite{}.testcase{}.status|table suite Testcase  Status

I wrote a query like this. but the problem here is in a single row multiple values will come. I want to break these value and print them in different row. Any optionnother than mvexpand?

[PickleRick]

That's why spath has both input and output options. And yes, you need to mvexpand your results to make each testcase a separate row.

[ITWhisperer]

You example is not correctly formatted JSON. Please provide a valid representative version of your events.

[anooshac]

{
    "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"
                },
               .
               .
               .
            ]
        }
    ]
}

Hi, @ITWhisperer ,Sorry for that, here is the correct formatted JSON data.

[ITWhisperer]

| spath suite{}.testcase{} output=testcase
| mvexpand testcase
| spath input=testcase
| table name status

[anooshac]

Hi, @ITWhisperer if mvexoand is used the results are truncated and i get a warning message. Any other alternative to mvexpand command is available?

[ITWhisperer]

Try removing / reducing unneeded fields before the doing the mvexpand to reduce the memory requirement

[PickleRick]

The most significant memory saving could come with doing

| fields - _raw

If you already have your fields parsed, there's no need to drag the whole huge raw event along.