How to correlate list of users based on IP?

[ff170a]

I am trying to create a report that will take a username(user) and look for the most recent IP address(src_ip) they used, then take that IP address that was found and look back 7 days for all events where the src_ip is the same. I've attempted to use join in different manners but have been unsuccessful.  Here is the latest try, where I get a return of everything, but I just need a complete list of users using that same IP.

Any help is appreciated, thanks!

index=firewall
|where isnotnull(src_ip)
|join type=left [ 
    | search (index=firewall user=mrusername)
    | eval ip=src_ip
    | where isnotnull(ip)
    | fields ip
    ]
| where ip=src_ip
|table user IP src_ip

[yuanliu]

Let me try to understand this.  You already have a fixed user name "mrusername".  You are looking at this user's latest src_ip, say in the past hour.  Then, you want to search for all events associated with this src_ip in the past 7 days from the same dataset.

Your illustrated code has the right thinking.  But there is no need for join. (In fact, join should always be the last resort.)  All you need is a subsearch that returns the last src_ip for mrusername.

index=firewall earliest=-7d 
    [search index=firewall user=mrusername earliest=-1h
    | stats latest(src_ip) as src_ip]

[gcusello]

Hi @ff170a,

did you tried:

index=firewall
| where isnotnull(src_ip)
| stats last(src_ip) AS src_ip BY user

Ciao.

Giuseppe