I created a join search for my environment where my 1st index is for my IPS and 2nd Index is for DHCP. DHCP index contains Hostname for my user machines.
I am joining IP addresses in both indexes and getting which Host is triggered in IPS.
My problem is after joining I am getting only the last value from my DHCP index.
That is supposed IP 126.96.36.199 was used by three hosts during the day; Host A, Host B, and Host C.
Host B is the host that was triggered in IPS at 12 PM, but Host C is the last host that used the IP at 4 PM.
Now when I check my join search at 5 PM it shows the threat in IPS was triggered at 12 PM with Hostname as Host C, which is wrong.
It needs to show Host B.
Is there any way I can fix this so that the correct host is showing for IPS threat in IPS index time?
Here is a sample of my search:
index=isp | join IP type=inner [search index=dhcp | fields _time,IP,HOSTNAME] | fillnull value=unkown | stats count by Threat,IP,Hostname