Splunk Search

Sorting the top 10 values of the each field that is grouped

renjujacob88
Path Finder

HI

I need to get top 10 values of the src_count on each grouped item. The query which i have is

index=palo | stats count by direction dest_port | stats values(dest_port) as dest_port list(count) as src_count sum(count) as total by direction | sort - total | sort 10 by src_count

alt text

Any help will be appreciated

1 Solution

cmerriman
Super Champion

will something like this work:

index=palo | stats count by direction dest_port |sort 0 direction dest_port - count|streamstats count as eventCount by direction|search eventCount>11| stats values(dest_port) as dest_port list(count) as src_count sum(count) as total by direction | sort - total | sort 10 by src_count

The one caveat is that if you have duplicate counts, they would get cut off. so if you have 11 of the same src_counts and they should all be in the top 10, it would only grab the first 10 of them.

View solution in original post

justdan23
Path Finder

Start with your initial base query result which is grouped by "destination":

index=palo | stats count by direction, dest_port | stats values(dest_port) as dest_port, list(count) as src_count, sum(count) as total by direction | sort direction, - src_count

Note: I'm assuming your query above provides you the answer of all info. Without source data, I only fixed syntax errors with comma usage.

Here, you are rolling up all dest_ports using "values", but might be needing "list" instead to keep all values and "dedup" them as needed. Assuming the query above provides the total set of records...

To query only for the first 10 records of every "direction" for the 10 greatest number of times ports were used for that "direction", you need to use "streamstats":

| streamstats count as eventCount by direction, - src_count | where eventCount < 11 | fields - eventCount

This results in the top 10 (or less) records for each "direction" grouped value.

0 Karma

cmerriman
Super Champion

will something like this work:

index=palo | stats count by direction dest_port |sort 0 direction dest_port - count|streamstats count as eventCount by direction|search eventCount>11| stats values(dest_port) as dest_port list(count) as src_count sum(count) as total by direction | sort - total | sort 10 by src_count

The one caveat is that if you have duplicate counts, they would get cut off. so if you have 11 of the same src_counts and they should all be in the top 10, it would only grab the first 10 of them.

renjujacob88
Path Finder

Your query Logic is right but need slight adjustment

index=palo | stats count by direction dest_port |sort - count|streamstats count as eventCount by direction| where eventCount <= 10| stats values(dest_port) as dest_port list(count) as src_count sum(count) as total list(eventCount) as eventCount by direction | table direction dest_port src_count total eventCount| sort 10 by src_count |sort - total |

0 Karma

cmerriman
Super Champion

oh my mistake on the search eventCount! completely fat fingered that! glad with some tweaks you got what you needed.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...