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.
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
I do not understand your problem.
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.
Is that >
supposed to be there? Why?
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
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.
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
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)?
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.
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
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 🙂