Convert json array to table

[Thulasiraman]

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

[Thulasiraman]

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"
    }
  ]
}

[danspav · Accepted solution]

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

[Thulasiraman]

Thanks. Solutions Works

[Thulasiraman]

Thanks. This works