Splunk cloud - extracting JSON data

[ashrafsj]

Hi All,

I have an JSON file that is ingested into Splunk, I need to create a dashboard with the different API's and their traffic

fields extracted are having the the timestamp as well and it makes it difficult to be used directly for the dashboard queries

data{}.fca-accounts-metrics-api-v1.08/23/2021.Number of Failure Traffic

Below is the extract of the JSON content

{
  "org": "xxx",
  "env": "prod",
  "from_date": "08/23/2021 00:00:00",
  "curr_date": "08/23/2021 23:59:59",
  "data": [
    {
      "management-api-v1": {
        "08/23/2021": {
          "Total Number of Traffic": "0.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "0.0"
        }
      }
    },
    {
      "sXXXX-api-v1": {
        "08/23/2021": {
          "Total Number of Traffic": "2113.0",
          "Average Request Turnaround(ms)": "57.68",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "2108.0"
        }
      }
    },
    {
      "sXX-api-v1": {
        "08/23/2021": {
          "Total Number of Traffic": "0.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "0.0"
        }
      }
    },
    {
      "open-banking-v31": {
        "08/23/2021": {
          "Total Number of Traffic": "0.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "0.0"
        }
      }
    },
    {
      "fca-accounting-metrics-api-v1": {
        "08/23/2021": {
          "Total Number of Traffic": "135.0",
          "Average Request Turnaround(ms)": "57.66",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "136.0"
        }
      }
    }

  ]
}

Is there a way to extract the API names and the traffic details. if this could be made to a tabular form with the date and api names and its traffic details it would be great to create a dashboard or chart. 

[ITWhisperer · Accepted solution]

You could try something like this to extract the fields

| makeresults 
| eval _raw="{
  \"org\": \"xxx\",
  \"env\": \"prod\",
  \"from_date\": \"08/23/2021 00:00:00\",
  \"curr_date\": \"08/23/2021 23:59:59\",
  \"data\": [
    {
      \"management-api-v1\": {
        \"08/23/2021\": {
          \"Total Number of Traffic\": \"0.0\",
          \"Average Request Turnaround(ms)\": \"0.0\",
          \"Number of Failure Traffic\": \"0.0\",
          \"Number of Success Traffic\": \"0.0\"
        }
      }
    },
    {
      \"sXXXX-api-v1\": {
        \"08/23/2021\": {
          \"Total Number of Traffic\": \"2113.0\",
          \"Average Request Turnaround(ms)\": \"57.68\",
          \"Number of Failure Traffic\": \"0.0\",
          \"Number of Success Traffic\": \"2108.0\"
        }
      }
    },
    {
      \"sXX-api-v1\": {
        \"08/23/2021\": {
          \"Total Number of Traffic\": \"0.0\",
          \"Average Request Turnaround(ms)\": \"0.0\",
          \"Number of Failure Traffic\": \"0.0\",
          \"Number of Success Traffic\": \"0.0\"
        }
      }
    },
    {
      \"open-banking-v31\": {
        \"08/23/2021\": {
          \"Total Number of Traffic\": \"0.0\",
          \"Average Request Turnaround(ms)\": \"0.0\",
          \"Number of Failure Traffic\": \"0.0\",
          \"Number of Success Traffic\": \"0.0\"
        }
      }
    },
    {
      \"fca-accounting-metrics-api-v1\": {
        \"08/23/2021\": {
          \"Total Number of Traffic\": \"135.0\",
          \"Average Request Turnaround(ms)\": \"57.66\",
          \"Number of Failure Traffic\": \"0.0\",
          \"Number of Success Traffic\": \"136.0\"
        }
      }
    }

  ]
}"


| spath path=data{} output=data
| mvexpand data
| table data
| rex mode=sed field=data "s/(?<f1>^\{\s+)(?<api>\"[^\"]+\")(?<f3>:\s+\{\s+)(?<date>\"[^\"]+\")(?<f5>:\s+\{\s+)/\1\"api\": \2, \"date\": \4, \"detail\": { \"detail\":{/g"
| spath input=data
| rename detail.detail.* as *

[ashrafsj]

@ITWhisperer  - the solution works fine for  1 day worth of data for the API's.  but when I try with 7 days/30 Days worth of data then it doesn't produce  the desired results. I was using soucetype = json_no_timestamp which eventually splits the json into multiple events when the json file is large.

At the moment with 10 API's the event is split into 2 events (for 7 days ) and when its 30 days its getting split in to multiple events.   

sample of 7 days data in JSON,

{
  "org": "xxx",
  "env": "prod",
  "from_date": "08/20/2021 00:00:00",
  "curr_date": "08/26/2021 23:59:59",
  "data": [
    {
      "management-api-v1": {
        "08/20/2021": {
          "Total Number of Traffic": "10.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "0.0"
        }
		"08/21/2021": {
          "Total Number of Traffic": "120.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "120.0"
        }
		"08/22/2021": {
          "Total Number of Traffic": "40.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "40.0"
        }
		"08/23/2021": {
          "Total Number of Traffic": "0.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "0.0"
        }
		"08/24/2021": {
          "Total Number of Traffic": "20.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "10.0"
        }
		"08/25/2021": {
          "Total Number of Traffic": "60.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "30.0",
          "Number of Success Traffic": "30.0"
        }"08/26/2021": {
          "Total Number of Traffic": "80.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "70.0"
        }
      }
    },
    {
      "sXXXX-api-v1": {
        "08/20/2021": {
          "Total Number of Traffic": "2158.0",
          "Average Request Turnaround(ms)": "50.00",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "2108.0"
        }
		"08/21/2021": {
          "Total Number of Traffic": "2113.0",
          "Average Request Turnaround(ms)": "57.68",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "2108.0"
        }
		"08/22/2021": {
          "Total Number of Traffic": "2113.0",
          "Average Request Turnaround(ms)": "57.68",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "2108.0"
        }
		"08/23/2021": {
          "Total Number of Traffic": "2113.0",
          "Average Request Turnaround(ms)": "57.68",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "2108.0"
        }
		"08/24/2021": {
          "Total Number of Traffic": "2113.0",
          "Average Request Turnaround(ms)": "57.68",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "2108.0"
        }
		"08/25/2021": {
          "Total Number of Traffic": "2113.0",
          "Average Request Turnaround(ms)": "57.68",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "2108.0"
        }
		"08/26/2021": {
          "Total Number of Traffic": "2113.0",
          "Average Request Turnaround(ms)": "57.68",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "2108.0"
        }
      }
    },
    {
      "sXX-api-v1": {
      "08/20/2021": {
          "Total Number of Traffic": "10.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "0.0"
        }
		"08/21/2021": {
          "Total Number of Traffic": "120.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "120.0"
        }
		"08/22/2021": {
          "Total Number of Traffic": "40.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "40.0"
        }
		"08/23/2021": {
          "Total Number of Traffic": "0.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "0.0"
        }
		"08/24/2021": {
          "Total Number of Traffic": "20.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "10.0"
        }
		"08/25/2021": {
          "Total Number of Traffic": "60.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "30.0",
          "Number of Success Traffic": "30.0"
        }"08/26/2021": {
          "Total Number of Traffic": "80.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "70.0"
        }
      }
    },
    {
      "open-banking-v31": {
        "08/20/2021": {
          "Total Number of Traffic": "10.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "0.0"
        }
		"08/21/2021": {
          "Total Number of Traffic": "120.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "120.0"
        }
		"08/22/2021": {
          "Total Number of Traffic": "40.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "40.0"
        }
		"08/23/2021": {
          "Total Number of Traffic": "0.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "0.0"
        }
		"08/24/2021": {
          "Total Number of Traffic": "20.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "10.0"
        }
		"08/25/2021": {
          "Total Number of Traffic": "60.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "30.0",
          "Number of Success Traffic": "30.0"
        }"08/26/2021": {
          "Total Number of Traffic": "80.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "70.0"
        }
      }
    },
    {
      "fca-accounting-metrics-api-v1": {
        "08/20/2021": {
          "Total Number of Traffic": "10.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "0.0"
        }
		"08/21/2021": {
          "Total Number of Traffic": "120.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "120.0"
        }
		"08/22/2021": {
          "Total Number of Traffic": "40.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "40.0"
        }
		"08/23/2021": {
          "Total Number of Traffic": "0.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "0.0",
          "Number of Success Traffic": "0.0"
        }
		"08/24/2021": {
          "Total Number of Traffic": "20.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "10.0"
        }
		"08/25/2021": {
          "Total Number of Traffic": "60.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "30.0",
          "Number of Success Traffic": "30.0"
        }"08/26/2021": {
          "Total Number of Traffic": "80.0",
          "Average Request Turnaround(ms)": "0.0",
          "Number of Failure Traffic": "10.0",
          "Number of Success Traffic": "70.0"
        }
      }
    }

  ]
}

[ashrafsj]

Thanks a lot @ITWhisperer  for a quick answer