- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Howdy,
I'm trying to come up with a query that charts the most occurring x_forwarded_for and respective count in each of the bins over whatever window. Currently, the below query creates a sorted chart of the most occurring x_forwarded_for and their respective count over the entire lookback window, instead of each bin. I think I need to fit head 1 in there somewhere.
It's likely some or all of the x_forwarded_for's across those bins are repeats and I'd like that charted, so no unique counts.
Any help is appreciated!
index="canvas_*" cluster="*" | where isnull(user_id)| bin _time span=5m | stats count by x_forwarded_for | sort - count
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

As I said, if you want top X from each bin, you have to count and filter 🙂
So instead of the | stats first... you do
| streamstats count as rank by _time
| where rank<=X
Oh, and if you have so many results, add limit of 0 to sort. Like instead of
| sort _time - count
do
| sort 0 _time - count
(i keep forgetting about it)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You start pretty well but then you lose the time information along the way
index="canvas_*" cluster=* NOT user_id=*
| bin _time span=5m
| stats count by x_forwarded_for _time
| sort _time - count
| stats first(*) by _time
If I understand correctly what you want to achieve.
If you want more than one "top" result, instead of stats first you'd need to do streamstats count to add rank order and do a where to limit results by this rank value.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you want more than one "top" result, instead of stats first you'd need to do streamstats count to add rank order and do a where to limit results by this rank value.
Should I interpret this as in more than one "top" result across the first bin or across all bins?
The query you provided almost gets me there. I was hoping it would get the top value across all bins. This seems to just return the highest count value for the first bin.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hmm. That should have given you top values for every bin. At least that's how I understood your specs.
Drop the last stats and see what you get.
You should get a table of counted x_forwarded_for values sorted by count, groupped by time (divided into 5-minute bins)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@PickleRick wrote:Hmm. That should have given you top values for every bin. At least that's how I understood your specs.
Drop the last stats and see what you get.
You should get a table of counted x_forwarded_for values sorted by count, groupped by time (divided into 5-minute bins)
x_forwarded_for | _time | count |
x.x.x.1 | 2022-02-10 13:20:00 | 191 |
x.x.x.2 | 2022-02-10 13:20:00 | 77 |
x.x.x.3 | 2022-02-10 13:20:00 | 66 |
every ip in the table is in the same 13:20:00 _time bin when I remove the last stat.
One thing that might be interesting to note, when I do execute the query with stats first(*) by _time, halfway through the execution, it seems like there's a chart with a row for every bin with an x_forwarded_for value and count associated with that _time bin. By the end of the execution, that list is compressed to a single row.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Ok. If you drop the last stats you should get the table like you showed.
After all the rows with decreasing counts at 2022-02-10 13:20:00, you should get similarily sorted entries from 13:25, then from 13:30 and so on (that's where stats first picks from).
Look at this:
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is there a way to only display the top 10 results from each bin? With my dataset, there are so many different x_forwarded_for that there are 100 pages (max return) with 100 x_forwarded_for ips per page for just the first bin in the lookback window.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

As I said, if you want top X from each bin, you have to count and filter 🙂
So instead of the | stats first... you do
| streamstats count as rank by _time
| where rank<=X
Oh, and if you have so many results, add limit of 0 to sort. Like instead of
| sort _time - count
do
| sort 0 _time - count
(i keep forgetting about it)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ohhhh, you really meant creating rank as a variable and using that. This is working like a charm!
You are a rockstar and have helped me better understand bin and TIL streamstats. Thank you very much PickleRick.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I'm happy I could have been of help 🙂
Have much fun with your splunk!
