Trouble with time in join

[jdmeek]

I have an index with events containing a src_ip but not a username for the event.   I have another index of VPN auth logs that has the assigned IP and username.  But the VPN IPs are randomly assigned.

I need to get the username from the VPN logs where vpn.client_ip matches event.src_ip.  But I need to make sure that the returned username is the one that was assigned during the event. 

In short, I need to get the last vpn client_ip assignment to match the event.src_ip BEFORE the event so the vpn.username would be the correct one for event.src_ip.

Here's a generic representation of my current query but I get nothing back.

index=event ... | join left=event right=vpn where event.src_ip=vpn.client_ip max=1 usetime=true earlier=true [search index=vpn]

[MuS · Accepted solution]

Hi there,

Take a look at this https://community.splunk.com/t5/Splunk-Search/How-to-compare-fields-over-multiple-sourcetypes-withou... 

Basically, what you need to do is use an eval to normalise the client IP:

| eval clientIp = coalesce(vpn.client_ip,matches event.src_ip)

and use a 'stats ... by clientIp'

Hope this helps ...

cheers, MuS

[jdmeek]

Thanks!