Splunk Search
×
Are you a member of the Splunk Community?
Sign in or Register with your Splunk account to get your questions answered, access valuable resources and connect with experts!
- Find Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Re: Splunk cloud - extracting JSON data
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
1 Solution
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ITWhisperer
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 *- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ITWhisperer
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 *- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
}
}
}
]
}
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ashrafsj
Path Finder
08-23-2021
07:23 AM
Thanks a lot @ITWhisperer for a quick answer
Get Updates on the Splunk Community!
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 ...
Index This | What goes away as soon as you talk about it?
May 2025 Edition
Hayyy Splunk Education Enthusiasts and the Eternally Curious!
We’re back with this month’s ...
What's New in Splunk Observability Cloud and Splunk AppDynamics - May 2025
This month, we’re delivering several new innovations in Splunk Observability Cloud and Splunk AppDynamics ...
