topic Re: Create a table with Json data in Dashboards & Visualizations

Create a table with Json data

anooshac — Mon, 07 Oct 2024 09:51:03 GMT

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 ?

Re: Create a table with Json data

ITWhisperer — Mon, 07 Oct 2024 10:24:16 GMT

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

Re: Create a table with Json data

anooshac — Mon, 07 Oct 2024 10:42:03 GMT

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

Re: Create a table with Json data

PickleRick — Mon, 07 Oct 2024 10:53:52 GMT

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.

Re: Create a table with Json data

ITWhisperer — Mon, 07 Oct 2024 10:59:01 GMT

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

Re: Create a table with Json data

anooshac — Mon, 07 Oct 2024 11:02:50 GMT

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

Re: Create a table with Json data

anooshac — Mon, 07 Oct 2024 11:40:40 GMT

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?

Re: Create a table with Json data

PickleRick — Mon, 07 Oct 2024 12:13:25 GMT

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.

Re: Create a table with Json data

ITWhisperer — Mon, 07 Oct 2024 12:59:47 GMT

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

Re: Create a table with Json data

PickleRick — Mon, 07 Oct 2024 13:12:32 GMT

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.