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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...