Query to search log-in failure more than 10 times in 1 minute.

■Overview

The below query searches IP addresses have failed log-in more than 10 times in 1 min. This is a good example shows how to use transactionize & merge operators.

■Query

_sourcecategory=Login and "LOGIN_ERROR" 
| _messagetime as mt 
| transactionize %"ipアドレス" maxspan=1m (merge %"ipアドレス" takeFirst, _raw join with "\n\n" as joined, mt takeLast as endmt, mt takeFirst as startmt, _group_size takeFirst as ログイン失敗回数)
| (startmt - endmt)/1000/60 as timediff_min
| formatDate(endmt, "MM/dd/yyyy HH:mm:ss") as endmt
| formatDate(startmt, "MM/dd/yyyy HH:mm:ss") as startmt
| where timediff_min <= 1
| where ログイン失敗回数 >= 10
| count by IPアドレス, startmt, endmt, ログイン失敗回数
| fields -_count

■Output

Reference
transactionize