Kibana Query Language summarize

Hello gurus,

I am new with Elastic and I have been searching to no avail. I am trying to create a SIEM rule for brute force. How do I write the query for this?

In Microsoft Sentinel, it will be

let threshold = 5;
TableName
| where EventID = 4025
| summarize 
startime = max(TimeGenerated), 
endtime = min(TimeGenerated),
count() as count by Username, EventID
Where count > 5

I am playing around with SIEM in Elastic but it seems to be catching every failed attempt which will be too noisy. Secondly, am I able to also aggregate alerts based on an entity; meaning one alert will be generated for user 1 and another alert for user 2 instead of 1 alert for user 1 + user 2.

Appreciate your help in this.

KQL always operates within a single document so you'll want to look at doing an aggregation first, the docs here have some examples Create an Elasticsearch query rule | Kibana Guide [8.16] | Elastic

Alternatively you could use ES|QL and there are some examples here ES|QL examples | Elasticsearch Guide [8.16] | Elastic with thresholds like five events of the same type, etc

For example:

FROM logs-*
| GROK dns.question.name "%{DATA}\\.%{GREEDYDATA:dns.question.registered_domain:string}"
| STATS unique_queries = COUNT_DISTINCT(dns.question.name) BY dns.question.registered_domain, process.name
| WHERE unique_queries > 10
| SORT unique_queries DESC
| RENAME unique_queries AS `Unique Queries`, dns.question.registered_domain AS `Registered Domain`, process.name AS `Process`
1 Like

I'm still improving my ES|QL knowledge so if you get stuck feel free to reply here and we can work through it together

Thanks for giving me some insights.

My raw log will be

message2024-11-25 08:45:59 UTC:ip-10-10-10-11.compute.internal(64610):test_user@TEST_VM:[21161]:LOG: EVENTID=4625@timestampNov 25, 2024

from logs-aws.cloudwatch_logs-default
| where loginFailure = message like /EVENTID=4625/
| where userName = regex(":(.)@", 1, message)
| where hostName = regex ("@(.
):", 1 ,message)
| stats loginFailureCount = count() by loginFailure, userName, hostName
| where loginFailureCount > 5

Will this query work out?

I believe that where is only used to reduce the result set, I think for the regex statements you'll want to use eval