当前位置：首页 > news >正文

ES聚合，SQL查询

news 2025/11/26 20:53:35

SQL查询


-- 多索引统计查询POST /_sql?format=txt
{"query": """SELECT COUNT(*) AS total,COUNT( rule_name) AS VALUE,rule_name AS NAME FROM "rule_log*,siem_log*" GROUP BY rule_name"""
}--  count 统计查询POST /_sql?format=txt
{"query": """SELECT rule_name,count(1) FROM "rule_log*" GROUP BY rule_name"""
}-- 条件统计查询POST /_sql?format=txt
{"query": """SELECT rule_name,log_model,COUNT(level.keyword = '严重' OR NULL) AS critical FROM rule_log GROUP BY rule_name,log_model"""
}-- list格式数据条件查询POST /_sql?format=txt
{"query": """SELECT logSourceName,logSourceIp,logSourceCitypeName,count(1) FROM "siem-event-2024.08.21" where depart_uuid in('element1','element333')   GROUP BY logSourceName,logSourceIp,logSourceCitypeName """
}-- 时间格式化查询POST /_sql?format=txt
{"query": """SELECT DATETIME_FORMAT("@timestamp",'yyyy-MM-dd') AS format_time,count(1) FROM "windows-log*" group by DATETIME_FORMAT("@timestamp",'yyyy-MM-dd') order by format_time desc """
}

聚合查询

ES常用的桶聚合如下：

Terms聚合 - 类似SQL的group by，根据字段唯一值分组
Histogram聚合 - 根据数值间隔分组，例如: 价格按100间隔分组，0、100、200、300等等
Date histogram聚合 - 根据时间间隔分组，例如：按月、按天、按小时分组
Range聚合 - 按数值范围分组
Elasticsearch 指标聚合（metrics）-函数
ES指标聚合，就是类似SQL的统计函数，指标聚合可以单独使用，也可以跟桶聚合一起使用。

常用的统计函数如下：

Value Count - 类似sql的count函数，统计总数
Cardinality - 类似SQL的count(DISTINCT 字段)，统计不重复的数据总数
Avg - 求平均值
Sum - 求和
Max - 求最大值
Min - 求最小值

POST rule_log/_search
{"size": 1, "aggs": {"聚合名称": {"terms": {"field": "rule_name.keyword","size": 10,"order": {"_key": "desc"}},"aggs" : {"type_count" : { "terms": { "field" : "level.keyword" }}}}}
}POST  windows-log-*/_search
{"size": 1, "query": {"bool": {"must": [{"range": {"@timestamp": {"gte": "2024-08-15T16:51:38","lte": "2024-08-15T17:21:38"}}}]}}, "aggs": {"聚合名称": {"terms": {"field": "host.ip","size": 10,"order": {"_key": "desc"}},"aggs" : {"type_count" : { "terms": { "field" : "model_name" }}}}}
}GET /siem_log/_search
{"size": 0,"query": {"bool": {"filter": [{}]}}, "aggs": {"date_histogram_aggs": {"date_histogram": {"field": "createTime","interval": "day","format": "yyyy-MM-dd","order": {"_key": "desc"}}}}
}POST siem_log/_search
{"size": 0, "aggs" : {"login_time" : { "date_histogram": { "field" : "createTime" ,"format": "yyyy-MM-dd","fixed_interval": "1d","offset": -28800000,"order": {"_key": "asc"},"keyed": false,"min_doc_count": 0,"extended_bounds": {   "min": "now/d", "max": "now/d"}}}}
}GET /sales/_search?size=0
{"aggs": {"types_count": { // 聚合查询的名字，随便取个名字"value_count": { // 聚合类型为：value_count"field": "type" // 计算type这个字段值的总数}}}
}等价SQL：select count(type) from sales