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
Any help will be appreciated
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.
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 |
oh my mistake on the
search eventCount! completely fat fingered that! glad with some tweaks you got what you needed.
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.