topic Re: Create a search for Hosts that failed Authentication this week but were Successful Last. in Splunk Search

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

atebysandwich — Fri, 28 Apr 2023 13:43:38 GMT

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

Re: Create a search for Hosts that failed Authentication this week but were Successful Last.

rut — Thu, 27 Apr 2023 19:13:05 GMT

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:

Success > Success (no result)
Fail > Success (no result)
Success > Fail (result)
Fail > Fail (no result)

Is this what you're looking for?

Re: Create a search for Hosts that failed Authentication this week but were Successful Last.

atebysandwich — Thu, 27 Apr 2023 19:32:58 GMT

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?

Re: Create a search for Hosts that failed Authentication this week but were Successful Last.

rut — Thu, 27 Apr 2023 19:43:24 GMT

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

Re: Create a search for Hosts that failed Authentication this week but were Successful Last.

atebysandwich — Thu, 27 Apr 2023 21:01:03 GMT

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?

Re: Create a search for Hosts that failed Authentication this week but were Successful Last.

rut — Thu, 27 Apr 2023 21:22:47 GMT

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"

Re: Create a search for Hosts that failed Authentication this week but were Successful Last.

atebysandwich — Fri, 28 Apr 2023 13:11:04 GMT

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.

Re: Create a search for Hosts that failed Authentication this week but were Successful Last.

rut — Fri, 28 Apr 2023 13:49:43 GMT

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.

Re: Create a search for Hosts that failed Authentication this week but were Successful Last.

atebysandwich — Fri, 28 Apr 2023 14:27:51 GMT

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