This query helps to identify ISPs where a successful sign-in was never completed, providing you with a list of malicious IPs associated, ISP country, and allowing you to set the threshold for malicious sign-in attempts.
Therefore… ISPs without success user connections and just failure attempts trying to take user credentials/tokens during last 30 days? Kick them out! 🏒
IdentityLogonEvents
| where Timestamp > ago(30d)
| project ISP, Location, IPAddress, FailureReason
| summarize valid = countif(isempty(FailureReason) or FailureReason contains "Success"), failure = countif( isnotempty(FailureReason) and FailureReason !contains "Success"), make_set(IPAddress) by ISP, Location
| order by failure
| where valid == 0 and failure > 5