I have a table that has the following fields:
The _time field shows the time the host was authenticated against for the current week and the previous.
How can I compare the Host_Auth field from the last two results for the same host? If the value for Host_Auth for a particular IP was successful last week by not this week, how can I show that?
IP Host_Auth _time
184.108.40.206 Unix Successful 2023-04-23 00:00:00
220.127.116.11 Unix Successful 2023-04-16 00:00:00
18.104.22.168 Unix Failed 2023-04-23 00:00:00
22.214.171.124 Unix Successful 2023-04-16 00:00:00
Hi @atebysandwich, counting the unique values in Host_Auth by IP and checking the last state may be sufficient:
| makeresults format=csv data=" IP,Host_Auth,_time 126.96.36.199,Unix Successful,2023-04-23 00:00:00 188.8.131.52,Unix Successful,2023-04-16 00:00:00 184.108.40.206,Unix Failed,2023-04-23 00:00:00 220.127.116.11,Unix Successful,2023-04-16 00:00:00 18.104.22.168,Unix Successful,2023-04-23 00:00:00 22.214.171.124,Unix Failed,2023-04-16 00:00:00 126.96.36.199,Unix Failed,2023-04-16 00:00:00 188.8.131.52,Unix Failed,2023-04-23 00:00:00 " | stats dc(Host_Auth) as unique_count, last(Host_Auth) as last_auth by IP | where unique_count>1 AND last_auth="Unix Failed"
I've added two more scenarios, so:
Is this what you're looking for?
Sorry for the confusion. You can ignore the makeresults command, I use it in my example to simulate the example data you provided. So it would look something like this:
.. your original search, containing the fields _time, Host_Auth and IP .. | stats dc(Host_Auth) as unique_count, last(Host_Auth) as last_auth by IP | where unique_count>1 AND last_auth="Unix Failed"
Weird. The only reason I can think of why this wouldn't work is sorting. Have you tried sorting your results descending before applying the rest?
| sort -_time | stats dc(Host_Auth) as unique_count, last(Host_Auth) as last_auth by IP | where unique_count>1 AND last_auth="Unix Failed"
The last function used in the stats command takes the last known value by IP, so if your results aren't sorted correctly that could mess things up.