Dashboards & Visualizations

Create a table with Json data

anooshac
Communicator

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 ?

Labels (2)
Tags (1)
0 Karma
1 Solution

anooshac
Communicator

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?

View solution in original post

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

anooshac
Communicator

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?

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

anooshac
Communicator

 

{
    "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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| spath suite{}.testcase{} output=testcase
| mvexpand testcase
| spath input=testcase
| table name status
0 Karma

anooshac
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...