How to create an alert if there are more than 5 login_id(s) associated to any unique sourceIP

[Log_wrangler]

I have an app behind a WAF, and I am looking at the WAF logs to see how many unique login IDs are coming from a single sourceIP.

so far I have

index=WAF sourcetype=waf_log  passed AND login | stats values(login_id) by sourceIP

Before the pipe "passed" and "login" criteria provide me with all interesting logs that contain a login_id and a sourceIP.

The challenge is finding the best way to list all IP(s) associated with a login_id, OR all the login_ids associated with a sourceIP, for the last 30 days.

Is there a better way to write this?

Ideally, I would like to create an alert if there are more than 5 login_id(s) associated to any unique sourceIP.

Thank you

[niketn · Accepted solution]

@Log_wrangler, when you are using values(login_id), you are trying to look at unique login_ids aggregated by sourceIP. So you can use dc(login_id) count of unique login ids for the sourceIP

 index=WAF sourcetype=waf_log  passed AND login 
| stats  dc(login_id) as uniqueLoginCount values(login_id) uniqueLoginIDs by sourceIP
| where uniqueLoginCount > 5

PS: list() will give you all occurrences of login_ids (including duplicates) for the same sourceIP, however, it will be restricted to first 100 matches. If you have less than 100 total logins per sourceIP, you can use count(login_id) and list(login_id). If you may have more than 100 total logins per sourceIP, you might have to use streamstats.

[wildcats12]

This seems like a good start for what you're trying to do. If you were to rename the 'values(login_id)' field that gets produced by this to something like 'id_list', you could filter down to only IPs with 5+ login_id(s) with:
| where mvcount(id_list) > 5