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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...