topic Re: Convert json array to table in Getting Data In

Convert json array to table

Thulasiraman — Mon, 23 Oct 2023 20:13:33 GMT

how to convert below json array to table
{

"Group10": {

"owner": "Abishek Kasetty",

"fail": 2,

"total": 12,

"agile_team": "Punchout_ReRun",

"test": "",

"pass": 6,

"report": "",

"executed_on": "Mon Oct 23 03:10:48 EDT 2023",

"skip": 0,

"si_no": "10"

"Group09": {

"owner": "Lavanya Kavuru",

"fail": 45,

"total": 190,

"agile_team": "Hawks_ReRun",

"test": "",

"pass": 42,

"report": "",

"executed_on": "Sun Oct 22 02:57:43 EDT 2023",

"skip": 0,

"si_no": "09"

}

}

Expected Output
________________________ ________________________ ________________________

agile_team pass fail

________________________ ________________________ ________________________

Hawks_ReRun 42 45

Re: Convert json array to table

danspav — Tue, 24 Oct 2023 01:42:00 GMT

Hi @Thulasiraman ,

Here's one way to create a table using some of Splunk's built-in JSON commands.

|makeresults | eval json="{ \"Group10\": { \"owner\": \"Abishek Kasetty\", \"fail\": 2, \"total\": 12, \"agile_team\": \"Punchout_ReRun\", \"test\": \"\", \"pass\": 6, \"report\": \"\", \"executed_on\": \"Mon Oct 23 03:10:48 EDT 2023\", \"skip\": 0, \"si_no\": \"10\" }, \"Group09\": { \"owner\": \"Lavanya Kavuru\", \"fail\": 45, \"total\": 190, \"agile_team\": \"Hawks_ReRun\", \"test\": \"\", \"pass\": 42, \"report\": \"\", \"executed_on\": \"Sun Oct 22 02:57:43 EDT 2023\", \"skip\": 0, \"si_no\": \"09\" }}" ``` Above is just to create the test data``` | eval keys = json_keys(json) | eval keys = json_array_to_mv(keys) | mvexpand keys | eval group = json_extract(json, keys) | fields - _time, json | spath input=group ``` Table out the fields you're interested in ``` | table agile_team, pass, fail

The search is doing the following:

Get all the "GroupXX" keys (assuming these change each time you run the search)
Convert the Group keys to a multivalue field
MVExpand the keys so there's one per event
Pull out each Group's values using the key
Run spath to convert the JSON to fields
Table out what you need

The output looks like this:

Cheers,

Daniel

Re: Convert json array to table

Thulasiraman — Thu, 09 Nov 2023 22:09:34 GMT

Thanks. This works

Re: Convert json array to table

Thulasiraman — Thu, 09 Nov 2023 22:09:59 GMT

Thanks. Solutions Works

Re: Convert json array to table

Thulasiraman — Thu, 09 Nov 2023 22:23:49 GMT

I'm new and learning, can you please help with below query

I have json file with below data, I would like to get name and status and display it in table. Help here is much appreciated. I'm new to splunk

Name Status
assetPortfolio_ValidateAddAssetForOthers passed
assetPortfolio_ValidatePLaceHolderText failure
assetPortfolio_ValidateIfFieldUpdated passed

{
"name": "behaviors",
"children": [
{
"name": "assetPortfolio_ValidateAddAssetForOthers",
"status": "passed"
},
{
"name": "assetPortfolio_ValidatePlaceHolderText",
"status": "failure"
},
{
"name": "assetPortfolio_ValidateIfFieldUpdated",
"status": "passed"
}
]
}