topic Re: Aggregating fields in JSON array in Splunk Search

Aggregating fields in JSON array

mbosse — Tue, 29 Sep 2020 09:06:45 GMT

I'm relatively new to Splunk queries. I have an event that contains JSON and within the JSON data is an array. There's some data about a web page request, then an array of resources that make up the page, with some timing data for each resource.

I'd like to get things like the resource with the longest duration for a given request, and the average duration across all resources for a given request.

Here's the data:

{
"ts":"2016-01-21T12:15:16.0451054-05:00",
"id":"a95dc052-de57-45a5-be8b-4eee1b7a39ec",
"url":"http://www.mysite.com/default.htm",
"res":
[
{
"r_tid":"a95dc052-de57-45a5-be8b-4eee1b7a39ec",
"r_nm":"http://www.mysite.com/file1.css",
"duration":8.2
},
{
"r_tid":"a95dc052-de57-45a5-be8b-4eee1b7a39ec",
"r_nm":"http://www.mysite.com/file2.css",
"duration":7.731
},
{
"r_tid":"a95dc052-de57-45a5-be8b-4eee1b7a39ec",
"r_nm":"http://www.mysite.com/file1.js",
"duration":16.909
}
]
}

And for max duration, something like this as the output:
r_nm, duration
http://www.mysite.com/file1.js, 16.909

and for the average, something like this:
url, average_duration
http://www.mysite.com/default.htm, 10.947

I've tried spath a number of different ways based on other posts in here, but can't seem to get the right syntax. Any help would be greatly appreciated. The t_id for each resource will always equal the id of the request, in case a join is needed to do this.

Re: Aggregating fields in JSON array

martin_mueller — Tue, 15 Mar 2016 22:04:43 GMT

Here's an idea:

| stats count | eval _raw = "{\"ts\": \"2016-01-21T12:15:16.0451054-05:00\",\"id\": \"a95dc052-de57-45a5-be8b-4eee1b7a39ec\",\"url\": \"http://www.mysite.com/default.htm\",\"res\": [{\"r_tid\": \"a95dc052-de57-45a5-be8b-4eee1b7a39ec\",\"r_nm\": \"http://www.mysite.com/file1.css\",\"duration\": 8.2}, {\"r_tid\": \"a95dc052-de57-45a5-be8b-4eee1b7a39ec\",\"r_nm\": \"http://www.mysite.com/file2.css\",\"duration\": 7.731}, {\"r_tid\": \"a95dc052-de57-45a5-be8b-4eee1b7a39ec\",\"r_nm\": \"http://www.mysite.com/file1.js\",\"duration\": 16.909}]}" 
| spath url | spath res{} output=res | mvexpand res | spath input=res

The first line generates a dummy event using your example, the second line extracts the url, splits the event per resource, and extracts the resource fields. (Side note: If you already have configured field extractions and see a url field you can drop the first spath.) After that, you can append one of these two to get your statistics:

| stats avg(duration) by url
| stats max(duration) by r_nm

Re: Aggregating fields in JSON array

mbosse — Tue, 29 Sep 2020 09:08:30 GMT

Thanks martin_mueller. When I used this on some real data, I had to add "| fields url, r_dur, r_nm" before the stats in order to actually get a value. I still don't fully understand when to include the fields clause, but that got what I needed. However, in the real data, on the one request event I'm validating with, there are 91 resources in the JSON array. For some reason, only the first 34 in the array are being used to calculate the average or the max. I'll likely post another question in here specific to that. Otherwise, I believe what you provided is working with the additional of the fields clause. Thank you.

Re: Aggregating fields in JSON array

mbosse — Fri, 18 Mar 2016 19:38:14 GMT

Oh - forgot to mention, if I use the dummy data approach and put exactly the same data with the 91 resources in the JSON array as was imported, it seems to work fine. So this tells me it's likely a limitation on the import, the indexing, or on the search (or maybe a combination). So again, thanks for your idea. I think what you proposed is a sound approach and now I have a different issue to work through.

Re: Aggregating fields in JSON array

mbosse — Fri, 18 Mar 2016 21:24:21 GMT

I did a little more digging and trouble shooting and found that during my import I was being presented with a warning on the entries that exceeded 10000 bytes. I determined the config setting that controls that and increased the truncate value in props.conf. Everything is working well now. Thanks again.

Re: Aggregating fields in JSON array

martin_mueller — Fri, 18 Mar 2016 21:46:38 GMT

Well done 🙂

Re: Aggregating fields in JSON array

paulwrussell — Mon, 23 May 2016 05:25:07 GMT

i still cant get real data to display anything other than a big count. didnt make a difference adding fields. what is feilds menat ot be doing in this scenario? why would eval behave different to real data?