topic Search for first event in Splunk Search

Search for first event

balcv — Wed, 30 Sep 2020 01:16:21 GMT

I would like to write a search of traffic data that will return _time,user,src_ip for the first occurrence. However, it is highly probably that a user will have multiple src_ip. (eg A user logins into a PC and a WiFi device).

So I am looking for a list of unique user / src_ip combinations showing the earliest instance the combination exists.

I currently have:

index="pan" (user="*\user1" AND > src_ip="192.168.*")

| stats earliest(_time) as firstEvent count by src_ip,user

| table firstEvent,user,src_ip

This returns the earliest combinations of user / src_ip disaplying the user and src_ip however I cannot get it to display the earliest _time each combination is found.

Many thanks.

Re: Search for first event

tiagofbmm — Wed, 10 Jul 2019 07:05:08 GMT

I think you need to look first into the user, which is the "static" part, and then look for each src_ip he has as it is dynamic:

Making it generic so you can get the results for any user and src_ip pair

index="pan" 
| stats earliest(_time) as firstEvent count by src_ip,user 
| table firstEvent,user,src_ip

This gives you the earliest time and count of each combination... Is this what you want? I'm not sure I'm not missing your goal here

Re: Search for first event

FrankVl — Wed, 30 Sep 2020 01:16:25 GMT

Can you please make more clear what you want and how that is different from what you get? because the search you are showing does exactly that: for each user/src_ip combination, show the earliest(_time) and the count.

Re: Search for first event

FrankVl — Wed, 10 Jul 2019 07:09:51 GMT

That's the exact same search as was mentioned in the question (apart from dropping some of the filtering for user and src_ip fields)?

Re: Search for first event

tiagofbmm — Wed, 10 Jul 2019 07:12:04 GMT

I understood the problem here was "each combination is found", where filtering on user AND src_ip would give just one. Anyway, that's why I asked for more clarification too, it's not clear whether we're missing the point

Re: Search for first event

jawaharas — Wed, 10 Jul 2019 07:12:23 GMT

You query seems to working fine with my sample data. I wonder what is missing. Anyway, I have formatted 'firstEvent' field (from epoch format) for easy readability.

| <base_query>
| stats earliest(_time) as firstEvent count by src_ip,user 
| eval firstEvent=strftime(firstEvent,"%Y-%m-%d %H:%M:%S")
| table firstEvent,user,src_ip

Re: Search for first event

FrankVl — Wed, 10 Jul 2019 07:15:30 GMT

He's not filtering for specific user or IP, he's filtering for a certain pattern in both fields. But totally agree with you that the question is not very clear 🙂

Re: Search for first event

woodcock — Wed, 10 Jul 2019 17:12:35 GMT

I do not understand your problem.

Re: Search for first event

balcv — Wed, 30 Sep 2020 01:16:47 GMT

The problem is that I do not get any results for _time. The output includes the unique user / src_ip combinations but does not show the earliest time it was detected in the search results. The column for firstEvent is blank.

Re: Search for first event

balcv — Wed, 30 Sep 2020 01:16:50 GMT

Re: Search for first event

woodcock — Thu, 11 Jul 2019 05:29:13 GMT

Try this:

index="pan" (user="*\user1" AND src_ip="192.168.*") 
| stats min(_time) as _time count last(_raw) As firstEvent by src_ip,user 
| table _time firstEvent,user,src_ip

Re: Search for first event

woodcock — Thu, 11 Jul 2019 05:30:00 GMT

Is that > supposed to be there? Why?