Search for first event

[balcv]

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.

[woodcock]

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

[woodcock]

I do not understand your problem.

[balcv]

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.

[woodcock]

Is that > supposed to be there? Why?

[jawaharas]

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

[FrankVl]

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.

[tiagofbmm]

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

[FrankVl]

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

[balcv]

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.

[tiagofbmm]

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

[FrankVl]

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 🙂