topic Re: Detecting Multiple Logins where distinct count in Splunk Search

Detecting Multiple Logins where distinct count

willadams — Wed, 30 Sep 2020 02:55:41 GMT

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

Service = myapp
Period of time is 5mins
Looking for a threshold of 30 (total count) where the count if for each user with a count of "1".
Location (source) is 101.101.101.101

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

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

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

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

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.

Re: Detecting Multiple Logins where distinct count

renjith_nair — Mon, 11 Nov 2019 13:12:23 GMT

@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

Re: Detecting Multiple Logins where distinct count

willadams — Tue, 12 Nov 2019 08:15:34 GMT

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.

Re: Detecting Multiple Logins where distinct count

FrankVl — Wed, 30 Sep 2020 02:56:27 GMT

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.

Re: Detecting Multiple Logins where distinct count

FrankVl — Tue, 12 Nov 2019 10:43:22 GMT

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.