I am trying to figure out how to create a search where I am using multiple counts for an alert I am wanting to write. The alert is effectively looking for a number of accounts failing authentication to a particular service.
An example query may be
index=windows user="USER*" signature="User name is correct but the password is wrong" NOT Source_Network_Address IN("192.168.0.1","-") | bucket _time span=5m | timechart count(Source_Network_Address) by Source_Network_Address
The problem with this search is that while it works, I am trying to expand on it further. It shows my a total count irrespective of the number of times an account has tried. My use case in this scenario is as follows:
I want to be able to identify where the total number of users exceeds a threshold value but the account was only tried once over a short period of time. So if I simply the scenario
So if looking at a log file as example
07:30 - 07:35
myapp account_login=User1
myapp account_login=User2
myapp account_login=User3
myapp account_login=User4
myapp account_login=User5
myapp account_login=User6
myapp account_login=User7
myapp account_login=User8
......
So if the count of User1=1 AND User2=1 AND User3=1 AND User4=1........etc. and the total count was 30 (so 30 users) then tell me about it.
I tried something along the lines of
index=windows user="USER*" signature="User name is correct but the password is wrong" NOT Source_Network_Address IN("192.168.0.1","-") User!=*$" | bucket _time span=5m | timechart count(Source_Network_Address) by User | where count > 30
or
index=windows user="USER*" signature="User name is correct but the password is wrong" NOT Source_Network_Address IN("192.168.0.1","-") User!=*$" | bucket _time span=5m | timechart dc(User) by Source_Network_Address | where count > 30
or
index=windows user="USER*" signature="User name is correct but the password is wrong" NOT Source_Network_Address IN("192.168.0.1","-") User!=*$" | bucket _time span=5m | stats dc(user) as TotalUser by user
or
index=windows user="USER*" signature="User name is correct but the password is wrong" NOT Source_Network_Address IN("192.168.0.1","-") User!=*$" | bucket _time span=5m | stats dc(user) as TotalUser by user
or
index=windows user="USER*" signature="User name is correct but the password is wrong" NOT Source_Network_Address IN("192.168.0.1","-") User!=*$" | bucket _time span=5m | stats count by user | timechart count(Source_Network_Address) by Source_Network_Address
But none of these work or I don't get any visualizations or tabulated data to show me the event.
If you want to find all Source_Network_Address instances that have more than 30 failed logons in 5min, but just one attempt per username, try this:
index=windows user="USER*" signature="User name is correct but the password is wrong" NOT Source_Network_Address IN("192.168.0.1","-") | bucket _time span=5m | stats dc(user) as usercount count by _time,Source_Network_Address | where usercount=count AND count>30
This counts the number of attempts and the distinct number of user names attempted per source address (per 5min window). If those 2 counts are the same, that means each attempt was for a unique user.
If you want to find all Source_Network_Address instances that have more than 30 failed logons in 5min, but just one attempt per username, try this:
index=windows user="USER*" signature="User name is correct but the password is wrong" NOT Source_Network_Address IN("192.168.0.1","-") | bucket _time span=5m | stats dc(user) as usercount count by _time,Source_Network_Address | where usercount=count AND count>30
This counts the number of attempts and the distinct number of user names attempted per source address (per 5min window). If those 2 counts are the same, that means each attempt was for a unique user.
@willadams, are you looking for something similar to this ?
index=windows user="USER*" signature="User name is correct but the password is wrong" NOT Source_Network_Address IN("192.168.0.1","-") User!=*$"
|timechart span=5m count(Source_Network_Address) by user
|addtotals row=true|where Total > 30
I altered my search so that it reads as follows:
index=windows user="USER*" signature="User name is correct but the password is wrong" Source_Network_Address IN("1.2.3.*","5.6.7.*") User!=*$" |timechart span=5m dc(user) by Source_Network_Adress | addtotals row=true | where Total > 30
My results end up with the following table
_time| 1.2.3.98 | 1.2.3.46 | 5.6.7.10 | 5.6.7.241 | Total
2019-11-12 10:30:00 | 1001 | 0 | 1 | 0 | 1002
2019-11-12 10:35:00 | 569 | 0 | 1 | 1 | 571
Is there a way that I can remove the values that are "0" or "1" as to only have the value 1.2.3.98 showing as this is the information I am interested in.
I tried the following
index=windows user="USER*" signature="User name is correct but the password is wrong" Source_Network_Address IN("1.2.3.*","5.6.7.*") User!=*$" |timechart span=5m dc(user) by Source_Network_Adress | addtotals row=true | eventstats sum(Source_Network_Address) by Source_Network_Address | where Total > 30
and the following
index=windows user="USER*" signature="User name is correct but the password is wrong" Source_Network_Address IN("1.2.3.*","5.6.7.*") User!=*$" |timechart span=5m dc(user) by Source_Network_Adress | stats count by Source_Network_Address as Total | where Total > 30
Both queries return no results found.
I suggest brushing up on your basics regarding the use of stats etc. You're doing impossible things. You can't sum ip addresses like sum(Source_Network_Address)
, you can't put as Total
at the end of a stats command after the by clause.