Splunk Search
Highlighted

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

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 destport | stats values(destport) as destport list(count) as srccount sum(count) as total by direction | sort - total | sort 10 by src_count

alt text

Any help will be appreciated

Highlighted

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

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

Highlighted

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

Path Finder

Your query Logic is right but need slight adjustment

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

0 Karma
Highlighted

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

Super Champion

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

0 Karma
Highlighted

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

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