Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
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!
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- 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!
Splunk Mobile: Your Brand-New Home Screen
Meet Your New Mobile Hub
Hello Splunk Community!
Staying connected to your data—no matter where you are—is ...
Introducing Value Insights (Beta): Understand the Business Impact your organization ...
Real progress on your strategic priorities starts with knowing the business outcomes your teams are delivering ...
Enterprise Security (ES) Essentials 8.3 is Now GA — Smarter Detections, Faster ...
As of today, Enterprise Security (ES) Essentials 8.3 is now generally available, helping SOC teams simplify ...
