Alerting on a one to many relationship

[ksinghg]

Hello,

I am trying to alert on failed login attempts in two scenarios:

1. When multiple IPs try and log into the same email
2. When one IP tries to log into multiple emails

My search string for 1 is as follows:

sourcetype="backend" | regex "User with email .* used an invalid password." | rex "User with email (?<email>.*) used an invalid password."  | rex "client_ip=(?<client_ip>\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b)" | transaction email maxspan=1000s | search eventcount > 5 | stats count(eval(client_ip)) as IPCount by email | where IPCount > 1

My search string for 2:

sourcetype="backend" | regex "User with email .* used an invalid password." | rex "User with email (?<email>.*) used an invalid password." | rex "client_ip=(?<client_ip>\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b)" | transaction client_ip maxspan=1000s | search eventcount > 2 | stats values(email), count(eval(email)) as EmailCount by client_ip | where EmailCount > 1

Both of these alert on "per result." They work as expected (the first sends an email showing one email and multiple IPs); however, when I increase either where IPCount > x or where EmailCount > x to anything greater than 1, I start to receive a flood of emails where there's only a one to one relationship (one email one IP). The email also leaves the "values(email)" column blank.

How can I do a search/ alert to achieve my desired goal?

Thanks.

[somesoni2]

Instead of using | stats count(eval(client_ip)) as IPCount by email, try using | stats dc(client_ip) as IPCount by email (same for other query).

[ksinghg]

The new search (below) is spamming my inbox again with results that don't map multiple emails to one IP as expected. Alerts return with a single email and single IP. The values(email) and EmailCount rows are left blank.

sourcetype="backend" | regex "User with email .* used an invalid password." | rex "User with email (?<email>.*) used an invalid password." | rex "client_ip=(?<client_ip>\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b)" | transaction client_ip maxspan=1000s | search eventcount > 2 | stats values(email), dc(email) as EmailCount by client_ip | where EmailCount > 2

[somesoni2]

Is this a real-time search or regular/historic search? Do you always want to consider attempt within 1000 seconds as valid failed attempt OR this can be any duration? What's the time range you're using?

[ksinghg]

This is a real time search. 1000 seconds is just for debugging. This can be any duration right now. Not using a time range.

[somesoni2]

If this can be any duration, give this a try.

sourcetype="backend" | regex "User with email .* used an invalid password." | rex "User with email (?<email>.*) used an invalid password." | rex "client_ip=(?<client_ip>\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b)" | stats values(email), dc(email) as EmailCount by client_ip | where EmailCount > 2

Also, once per result option is available with regular/historical search as well, so do you think you can run a regular, more frequently alert, instead of using real-time alert? The real-time searches are expensive.

[ksinghg]

I could, I just want to get the real-time approach working first. I just tried the above example and got a bunch of inaccurate alerts again.

[somesoni2]

Can you try it with regular search?

Even with real-time there should be a time window that you must be looking at, what is it (may be look at job inspector to see what time range is being applied).

[ksinghg]

Hello, I tried it with regular search and nothing came up. However, for the above search strings, even with a IPCount / EmailCount > 2, the realtime results are correct but the email alerts are wrong.

[ksinghg]

Thanks I'll try it out. Can you explain the difference as to why count() wouldn't work but dc() would?