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.
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 *
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 *
@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"
}
}
}
]
}
Thanks a lot @ITWhisperer for a quick answer