https://community.splunk.com/t5/Splunk-Search/DHCP-and-Proxy-Search-Join-Performance-Issues/m-p/303654#M91341 <P>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?</P> <PRE><CODE>index=proxy | join ip usetime=true earlier=true [ search index=dhcp dest_hostname="computername"| rename dest as ip] </CODE></PRE> Fri, 19 May 2017 14:04:32 GMT michaeldeck 2017-05-19T14:04:32Z https://community.splunk.com/t5/Splunk-Search/DHCP-and-Proxy-Search-Join-Performance-Issues/m-p/303654#M91341 <P>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?</P> <PRE><CODE>index=proxy | join ip usetime=true earlier=true [ search index=dhcp dest_hostname="computername"| rename dest as ip] </CODE></PRE> Fri, 19 May 2017 14:04:32 GMT https://community.splunk.com/t5/Splunk-Search/DHCP-and-Proxy-Search-Join-Performance-Issues/m-p/303654#M91341 michaeldeck 2017-05-19T14:04:32Z https://community.splunk.com/t5/Splunk-Search/DHCP-and-Proxy-Search-Join-Performance-Issues/m-p/303655#M91342 <P>Try this:</P> <PRE><CODE>index=proxy OR (index=dhcp dest_hostname="computername" | eval ip=coalesce(ip, dest) | fields - dest | stats list(*) AS * BY ip </CODE></PRE> Fri, 19 May 2017 23:01:16 GMT https://community.splunk.com/t5/Splunk-Search/DHCP-and-Proxy-Search-Join-Performance-Issues/m-p/303655#M91342 woodcock 2017-05-19T23:01:16Z https://community.splunk.com/t5/Splunk-Search/DHCP-and-Proxy-Search-Join-Performance-Issues/m-p/303656#M91343 <P>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".</P> Mon, 22 May 2017 12:47:58 GMT https://community.splunk.com/t5/Splunk-Search/DHCP-and-Proxy-Search-Join-Performance-Issues/m-p/303656#M91343 michaeldeck 2017-05-22T12:47:58Z https://community.splunk.com/t5/Splunk-Search/DHCP-and-Proxy-Search-Join-Performance-Issues/m-p/303657#M91344 <P>This should do it:</P> <PRE><CODE>index=proxy [ search index=dhcp dest_hostname="computername"| stats values(dest) AS ip ] </CODE></PRE> Fri, 26 May 2017 01:41:34 GMT https://community.splunk.com/t5/Splunk-Search/DHCP-and-Proxy-Search-Join-Performance-Issues/m-p/303657#M91344 woodcock 2017-05-26T01:41:34Z