Getting Data In

Convert json array to table

Thulasiraman
Explorer

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

Labels (1)
0 Karma
1 Solution

danspav
SplunkTrust
SplunkTrust

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:

danspav_0-1698111448611.png



Cheers,

Daniel

View solution in original post

Thulasiraman
Explorer

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

0 Karma

danspav
SplunkTrust
SplunkTrust

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:

danspav_0-1698111448611.png



Cheers,

Daniel

Thulasiraman
Explorer

Thanks. Solutions Works

0 Karma

Thulasiraman
Explorer

Thanks. This works

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...