Head for each value of a field

[PickleRick]

Hi there.

I'm relatively new to searching in Splunk so I can't sometimes get my head wrapped up around some Splunk concepts 😉

Anyway. I'm trying to achieve something and I can't seem to be able to get it right.

Let's assume I have an index containing some login events into the system. It contains a username column, a timestamp (obviously) and an IP of a remote endpoint.

What I need is to make a table containing last n login entries for each user. So I'd love to have something like (assuming limiting to 3 latest entries):

login1	IP1	time1
login1	IP2	time2
login1	IP3	time3
login2	IP4	time4
login2	IP5	time5
...	...	...

I tried using "| stats list" but (apart from splunk shouting at me for exceeding some list limits) it makes a multivalue field which I cannot further process (for example by geoip).

Any attempt to combine sort and head ends up with limiting the data without taking into account distinct values of a login column. So that's not what I want. Time should indeed be monotonic and sorted but only within single login. In the case above, there is no strict rule between times 1-3 and, let's say, 4-6. So simple sorting doesn't work.

Foreach sounded nicely but it seems it's for completely different use cases (it iterates over columns, not over values).

Any other hints?

[PickleRick]

It is an old thread but I feel with more experience under my belt I should fix the solution.

Map is hardly ever _the_ solution so in this case it isn't either. It's better to count and filter.

index=logins
| streamstats count by login
| where count<=5

And that's it.

[martin_mueller · Accepted solution]

Well if we are unearthing this then:

Index=logins | dedup 5 login

[PickleRick]

OK. Found it 🙂

It seems I need the map command.

Something like:

index=logins
| fields login
| dedup login
| map search="search index=logins login=\"$login$\" | head 5"

It's a bit ugly 😉 but seems to work.

[gcusello]

Hi @PickleRick,

you have to use the stats command using the last option, something like this:

your_search
| stats last(IP) AS IP latest(_time) AS _time BY user

I hint to use some time for the Splunk Fundamentals I Training (https://www.splunk.com/en_us/training/free-courses/splunk-fundamentals-1.html)

and Splunk Search Tutorial (https://docs.splunk.com/Documentation/Splunk/8.1.2/SearchTutorial/WelcometotheSearchTutorial), then there are many videos on YouTube.

Ciao.

Giuseppe

[PickleRick]

Unfortunately, last() and latest() give you a single value. I need to return a number of rows.  And that's where I'm stuck.

I started the fundamentals but got distracted. 😉