Splunk Search

Head for each value of a field

PickleRick
SplunkTrust
SplunkTrust

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):

login1IP1time1
login1IP2time2
login1IP3time3
login2IP4time4
login2IP5time5
.........

 

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?

Labels (2)
Tags (1)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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. 😉

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...