DHCP and Proxy Search - Join Performance Issues

[michaeldeck]

I have a working search using join that correlates DHCP addresses by machine name to find web proxy traffic as the device changes IP addresses. Join is quite slow though. Is there a way to do a faster search that provides similar results with something like stats or eventstats instead of join?

index=proxy | join ip  usetime=true earlier=true [ search index=dhcp dest_hostname="computername"| rename dest as ip]

[woodcock · Accepted solution]

This should do it:

index=proxy [ search index=dhcp dest_hostname="computername"| stats values(dest) AS ip ]

[woodcock]

Try this:

index=proxy OR (index=dhcp dest_hostname="computername"
| eval ip=coalesce(ip, dest)
| fields - dest
| stats list(*) AS * BY ip

[michaeldeck]

When I attempted to run this, it seems to return every proxy event sorted by IP instead of just the ones relative to IPs just used by "computername".