How to create a search for Hosts that failed Authentication this week but were successful last?

[atebysandwich]

I have a table that has the following fields:

IP
Host_Auth

_time 

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?

Eample

IP                        Host_Auth                         _time 
1.1.1.1               Unix Successful             2023-04-23 00:00:00          
1.1.1.1                Unix Successful            2023-04-16 00:00:00  

2.2.2.2                Unix Failed                       2023-04-23 00:00:00 
2.2.2.2                 Unix Successful            2023-04-16 00:00:00 

[rut]

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 
1.1.1.1,Unix Successful,2023-04-23 00:00:00          
1.1.1.1,Unix Successful,2023-04-16 00:00:00
2.2.2.2,Unix Failed,2023-04-23 00:00:00 
2.2.2.2,Unix Successful,2023-04-16 00:00:00 
3.3.3.3,Unix Successful,2023-04-23 00:00:00 
3.3.3.3,Unix Failed,2023-04-16 00:00:00 
4.4.4.4,Unix Failed,2023-04-16 00:00:00 
4.4.4.4,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:

1. Success > Success (no result)
2. Fail > Success (no result)
3. Success > Fail (result)
4. Fail > Fail (no result)

Is this what you're looking for?

[atebysandwich]

HI, this seems like it could work but I need to do this for over 8k IPs. Is there a way to do this without having to specify each one?

[rut]

I added the makeresults command to generate my testdata, you should replace that line with your own search.

[atebysandwich]

I'm sorry but I don't quite understand. There is about 20 lines before the example fields I provided in my question. Do i put the makeresults before that?

[rut]

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"

[atebysandwich]

I got results but it doesn't seem to be accurate. For example, an IP showed up that was successful this week but not last week. What I'm looking for is the exact opposite. 

[rut]

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.

[atebysandwich]

That didn't quite work either. There was only 1 result but over 1k of the 8k scanned failed this past week.