# Kibana Query Language summarize
**URL:** https://discuss.elastic.co/t/kibana-query-language-summarize/370825
**Category:** SIEM
**Created:** [November 20, 2024, 9:21am UTC](https://discuss.elastic.co/t/kibana-query-language-summarize/370825 "2024-11-20T09:21:29Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@fitastronaut](https://discuss.elastic.co/u/fitastronaut)
#### Post date: [November 20, 2024, 9:21am UTC](https://discuss.elastic.co/t/kibana-query-language-summarize/370825/1 "2024-11-20T09:21:29Z")
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
```auto
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.
---
### Author:  [@strawgate](https://discuss.elastic.co/u/strawgate)
#### Post date: [November 20, 2024, 7:47pm UTC](https://discuss.elastic.co/t/kibana-query-language-summarize/370825/2 "2024-11-20T19:47:31Z")
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](https://www.elastic.co/guide/en/kibana/current/rule-type-es-query.html)
Alternatively you could use ES|QL and there are some examples here [ES|QL examples | Elasticsearch Guide [8.16] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-examples.html) with thresholds like five events of the same type, etc
For example:
```auto
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`
```
---
### Author:  [@strawgate](https://discuss.elastic.co/u/strawgate)
#### Post date: [November 20, 2024, 8:31pm UTC](https://discuss.elastic.co/t/kibana-query-language-summarize/370825/3 "2024-11-20T20:31:49Z")
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
---
### Author:  [@fitastronaut](https://discuss.elastic.co/u/fitastronaut)
#### Post date: [November 25, 2024, 3:23am UTC](https://discuss.elastic.co/t/kibana-query-language-summarize/370825/4 "2024-11-25T03:23:05Z")
Thanks for giving me some insights.
My raw log will be
```auto
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?
---
### Author:  [@strawgate](https://discuss.elastic.co/u/strawgate)
#### Post date: [November 25, 2024, 3:54am UTC](https://discuss.elastic.co/t/kibana-query-language-summarize/370825/5 "2024-11-25T03:54:54Z")
I believe that where is only used to reduce the result set, I think for the regex statements you'll want to use `eval`
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [December 23, 2024, 3:54am UTC](https://discuss.elastic.co/t/kibana-query-language-summarize/370825/6 "2024-12-23T03:54:56Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.