Nested Splunk Query - Time of event within consolidate events

[JgTheGreat]

Hello All,

Sorry relativly new to splunk - and so this query may be a pile of garbage! To sumerise, i have a query here which is looking for brute force attacks against my website. The criteria is 5> Unique failed attempted Users, with 1+ Successful usernames over a given time period. I would like to display as part of this query, the time at which that successful connection occurred.

..... | dedup _raw,_time,source,host | dedup USERNAME,IPADDRESS | eval SuccessUSERNAME= if((MESSAGE="login"),USERNAME,"") | eval FailedUSERNAME= if((MESSAGE="FAILED"),USERNAME,"")      
|stats count(eval(MESSAGE="FAILED")) as FailedLogon, count(eval(MESSAGE="login")) as SuccessfulLogon, values(SuccessUSERNAME),   by IPADDRESS | search FailedLogon>5 SuccessfulLogon>=1

Tried numerous things; suspect that a nested query would be required, but as my knowledge of splunk is very limited, and any help would be much appreciated!

[gcusello]

Hi JgTheGreat,
why don't your try something more easy,:

index=your_index (MESSAGE="login" OR MESSAGE="FAILED")
| stats count by USERNAME IPADDRESS
| where count>5

You coulr run this search as an alarm every 5 minutes (or a smaller period).
In this way you're alerted both if there are five logfailed and one login or 5 logfailed

Bye.
Giuseppe

[JgTheGreat]

Not sure that i've fully explained the query. I'm after the number of unique accounts that are seen attempting to login over that time period.

The query is specifically looking for credential stuffing, where the credentials were correct. Make sense?

[gcusello]

I usually search many failed logins to find a brute force attack.

If instead you want to know which creadentials were stuffed, you can add a condition:

  index=your_index (MESSAGE="login" OR MESSAGE="FAILED")
 | stats count by USERNAME IPADDRESS
 | where count>5 AND MESSAGE="login"

Bye.
Giuseppe