How to group similar field values based on the sum of two other fields in the logs

[samble]

I have a log file where one of the fields is the category name (similar fields include IP, host, user, URL) and the other two fields are bytes in and bytes out. I would like to create a dashboard to display the top 5 values for the category field based on the sum of bytes in + bytes out. I also would like to convert the bytes into MB for readability. How can I get this accomplished? Thanks

I was able to create a dashboard based on count, which was easy, but not sure to get a dashboard based on the total of bytes in and bytes out.

[sduff_splunk · Accepted solution]

<base search> | eval total=bytes_in + bytes_out | eval total_mb=total/1048576 | stats max(total_mb) by category

Base search is whatever you need to do to get the logs
The first eval just sums the byte counts together
The second eval converts a byte count to a mb count
Finally, the stats command finds the top value for each category

[samble]

This is what got the results I wanted:

sourcetype="webstat" |eval total=bytes_in + bytes_out | eval total_mb=total/1048576 |stats sum(total_mb) by Category| sort 5 -sum(total_mb)

[samble]

Thanks. How do I just limit it to the top five categories based on total_mb? Also should I use stats sum(total_mb) since I want the sum of bytes in and bytes out for each category

[samble]

I figured how to just list the top 5
| sort 5 -sum(total_mb)

[ppablo]

Hi @samble

If this has fully answered your question, don't forget to resolve the post by clicking "Accept" directly below the answer and upvote users who have helped you out with finding your solution.

[sduff_splunk]

yes, you should replace it with stats sum(total_mb) by category

I would recommend that you sum the bytes first, before converting to MB. This avoids any data loss by rounding your data to MB. Your final query would be

 <base search> | eval total=bytes_in + bytes_out | stats sum(total) as total by category | eval total=total/1048576

[samble]

Thanks. Any way to limit this to the top 5 categories by total for doing a dashboard?

[sduff_splunk]

  <base search> | eval total=bytes_in + bytes_out | stats sum(total) as total by category | sort - total | head 5 | eval total=total/1048576

We sort the data based on the total (the - makes it decreasing, so largest first)
The head 5 gives only the first 5 results

[sduff_splunk]

Maybe you could share examples from each of your log files, which would help in developing a query to group your data

[samble]

<159>Jul 15 22:19:30 category=18 user=xxxx src_host=xxx.xx.xx.xxx src_port=63344 dst_host=1v1.tsn.ca dst_ip=50.116.55.203 dst_port=80 bytes_out=1133 bytes_in=370 http_response=304
http_proxy_status_code=304 reason=- disposition=1026 url=http://1v1.tsn.ca/assets/vendor/modernizr-50e63df78e278bef5a9c8dab29c3f23d.js