Splunk Search
×

Join the Conversation

Without signing in, you're just watching from the sidelines. Sign in or Register to connect, share, and be part of the Splunk Community.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ask a Question
Solved! Jump to solution

Splunk cloud - extracting JSON data

ashrafsj
Path Finder
‎08-23-2021 05:37 AM

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. 

Labels (4)
Labels
0 Karma
Reply
1 Solution
Solved! Jump to solution
Solution

ITWhisperer
SplunkTrust
SplunkTrust
‎08-23-2021 06:48 AM

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 *

View solution in original post

Reply
Solved! Jump to solution
Solution

ITWhisperer
SplunkTrust
SplunkTrust
‎08-23-2021 06:48 AM

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 *
Reply
Solved! Jump to solution

ashrafsj
Path Finder
‎08-26-2021 09:05 AM

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

  ]
}

 



 

Tags (1)
0 Karma
Reply
Solved! Jump to solution

ashrafsj
Path Finder
‎08-23-2021 07:23 AM

Thanks a lot @ITWhisperer  for a quick answer

0 Karma
Reply
Get Updates on the Splunk Community!

Splunk Enterprise Security: Your Command Center for PCI DSS Compliance

Every security professional knows the drill. The PCI DSS audit is approaching, and suddenly everyone's asking ...

Developer Spotlight with Guilhem Marchand

From Splunk Engineer to Founder: The Journey Behind TrackMe    After spending over 12 years working full time ...

Cisco Catalyst Center Meets Splunk ITSI: From 'Payments Are Down' to Root Cause in ...

The Problem: When Networks and Services Don't Talk Payment systems fail at a retail location. Customers are ...