Splunk Search

How to combine values from stats into single row?

TribesmanJohn
Explorer

Hi All,

I am looking into using some proxy logs to determine download volume for particular streaming sites and was looking for a way to merge hostnames into one "service".

Consider the SPL:

index=proxy url=*.streaming-site.com
| eval megabytes=round(((bytes_in/1024)/1024),2)
| stats sum(megabytes) as Download_MB by url
| sort -Download_MB

Will likely return multiple rows like:

cdn1.streaming-site.com    180.3
cdn2.streaming-site.com 164.8
www.streaming-site.com  12.3

I am wanting to merge those all into one row of

streaming-site.com   357.4

I have played around with the coalesce function, but this would be unsustainable for sites like Netflix which have dozens of URLs associated with them.

If anyone has any suggestions on how I might combine results with say a wildcard (*), I'd love to hear from you!

Labels (3)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The coalesce function selects a field within a single result.  To combine (aggregate) multiple results, use the stats command again after modifying the url field.

index=proxy url=*.streaming-site.com
| eval megabytes=round(((bytes_in/1024)/1024),2)
| stats sum(megabytes) as Download_MB by url
| eval url=replace(url, ".*?\.(.*)","\1")
| stats sum(Download_MB) as Download_MB by url
| sort - Download_MB

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

TribesmanJohn
Explorer

Thanks for this! 🙂

More tweaking required on my part as some of the subdomains being evaluated have more than 3 levels, but this is a big help in getting me on the right track!

richgalloway
SplunkTrust
SplunkTrust

The coalesce function selects a field within a single result.  To combine (aggregate) multiple results, use the stats command again after modifying the url field.

index=proxy url=*.streaming-site.com
| eval megabytes=round(((bytes_in/1024)/1024),2)
| stats sum(megabytes) as Download_MB by url
| eval url=replace(url, ".*?\.(.*)","\1")
| stats sum(Download_MB) as Download_MB by url
| sort - Download_MB

 

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Uncovering Multi-Account Fraud with Splunk Banking Analytics

Last month, I met with a Senior Fraud Analyst at a nationally recognized bank to discuss their recent success ...

Secure Your Future: A Deep Dive into the Compliance and Security Enhancements for the ...

What has been announced?  In the blog, “Preparing your Splunk Environment for OpensSSL3,”we announced the ...

New This Month in Splunk Observability Cloud - Synthetic Monitoring updates, UI ...

This month, we’re delivering several platform, infrastructure, application and digital experience monitoring ...