ES聚合，SQL查询

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

java API（es sql 查询放方式）

package com.suninfo.common.divreport.esreport.execute;

import cn.hutool.db.sql.SqlBuilder;
import co.elastic.clients.elasticsearch.ElasticsearchClient;
import co.elastic.clients.elasticsearch.sql.QueryResponse;
import com.suninfo.common.divreport.chart.SChart;
import com.suninfo.common.divreport.esreport.esmapper.ESMapper;
import com.suninfo.common.divreport.esreport.esquery.ESQuery;
import com.suninfo.common.divreport.esreport.factory.ESMapperFactory;
import com.suninfo.common.divreport.report.QuerySql;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ObjectUtils;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

@Slf4j
public class ElasticExecute implements ESExecute {

    private ESQuery esQuery;

	private ElasticsearchClient esClient;

    public ElasticExecute(ESQuery esQuery, ElasticsearchClient esClient) {
        this.esQuery = esQuery;
        this.esClient = esClient;
    }

	@Override
	public List<SChart> execute() {
		List<QuerySql> esQuerySqlList = esQuery.getEsSqlList();
		List<SChart> sChartList = new ArrayList<>();

		for (QuerySql querySql : esQuerySqlList) {
			SqlBuilder sqlBuilder = querySql.getSqlBuilder();
			// 上下文中设置当前的querySql
			esQuery.getQueryContent().setCurrentQuerySql(querySql);

			log.error("sql:{} {}", sqlBuilder.build(), sqlBuilder.getParamValues());
			String sql = getExecuteSql(sqlBuilder);
			QueryResponse queryResponse;
			try {
				queryResponse = esClient.sql().query(e -> e.query(sql));
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
			ESMapper sMapper = ESMapperFactory.getSMapper(esQuery.getQueryContent());
			SChart sChart = sMapper.toChart(queryResponse);
			sChartList.add(sChart);
		}
		return sChartList;
	}

	private String getExecuteSql(SqlBuilder sqlBuilder){
		String sql = sqlBuilder.build();
		if (sql.contains("?")) {
			for (Object paramValue : sqlBuilder.getParamValues()) {
				if (sql.indexOf("?") != -1) {
					sql = sql.substring(0, sql.indexOf("?")) + "\'" + paramValue + "\'" + sql.substring(sql.indexOf("?") + 1, sql.length());
				}
			}
		}
		return sql;
	}



}

package org.jeecg.common.es;

import co.elastic.clients.elasticsearch._types.FieldValue;
import co.elastic.clients.elasticsearch._types.query_dsl.*;
import org.jeecg.common.system.vo.SysPermissionDataRuleModel;
import co.elastic.clients.json.JsonData;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * ES权限控制工具类
 * Author: WangLiang
 * Date: 2023/10/7 11:12
 */
public class ESDataPermissionUtil {


    /**
     * 根据提供的条件map和构造的query拼接过滤条件
     *
     * @param dataRoleMap
     * @param boolQuery
     */
    public static void addFilterRule(Map<String, List<SysPermissionDataRuleModel>> dataRoleMap, BoolQuery.Builder boolQuery) {
        // 过滤数据权限
        // 对拿到的数据进行分组，每一组之间用 and 分割，组内用 or 分割；
        // 例： where (condition1 = x or condition2 < x or condition3 < x) and (condition4 = x or condition5 = x)

        for (Map.Entry<String, List<SysPermissionDataRuleModel>> entry : dataRoleMap.entrySet()) {
            BoolQuery.Builder boolGroupQuery = new BoolQuery.Builder();
            List<SysPermissionDataRuleModel> dataList = entry.getValue();
            List<Query> queryList = new ArrayList<>();
            for (SysPermissionDataRuleModel model : dataList) {
                queryList.add(ruleConvertFilter(model));
            }
            boolGroupQuery.should(queryList);
            boolQuery.filter(f->f.bool(boolGroupQuery.build()));
        }
    }

    /**
     * 根据系统存入的数据过滤规则转换为ES的过滤规则
     * @param model 系统model
     * @return ES的query对象
     */
    private static Query ruleConvertFilter(SysPermissionDataRuleModel model) {
        String condition = model.getRuleConditions();

        Query.Builder query = new Query.Builder();
        if (condition.equals("=")) {
            query.term(t -> t.field(model.getRuleColumn()).value(model.getRuleColumnvalue()));
        } else if (condition.equals(">")) {
            query.range(r -> r.field(model.getRuleColumn()).gt(JsonData.of(model.getRuleColumnvalue())));
        } else if (condition.equals(">=")) {
            query.range(r -> r.field(model.getRuleColumn()).gte(JsonData.of(model.getRuleColumnvalue())));
        } else if (condition.equals("!=")) {
            query.bool(q -> q.mustNot(m -> m.term(t -> t.field(model.getRuleColumn()).value(model.getRuleColumnvalue()))));
        } else if (condition.equals("IN")) {
            List<FieldValue> fields = new ArrayList<>();
            String[] values = model.getRuleColumnvalue().split(",");
            for (String value : values) {
                fields.add(FieldValue.of(value));
            }
            query.terms(t -> t.field(model.getRuleColumnvalue()).terms(ms -> ms.value(fields)));
        } else if (condition.equals("LIKE")) {
            query.wildcard(m -> m.field(model.getRuleColumn()).value("*" + model.getRuleColumnvalue() + "*"));
        } else if (condition.equals("LEFT_LIKE")) {
            query.wildcard(m -> m.field(model.getRuleColumn()).value("*" + model.getRuleColumnvalue()));
        } else if (condition.equals("RIGHT_LIKE")) {
            query.wildcard(m -> m.field(model.getRuleColumn()).value(model.getRuleColumnvalue() + "*"));
        } else if (condition.equals("<")) {
            query.range(r -> r.field(model.getRuleColumn()).lt(JsonData.of(model.getRuleColumnvalue())));
        } else if (condition.equals("<=")) {
            query.range(r -> r.field(model.getRuleColumn()).lte(JsonData.of(model.getRuleColumnvalue())));
        }

        return query.build();
    }

   public void test(){
		  BoolQuery.Builder boolQuery = new BoolQuery.Builder();
		  boolQuery.must(
		      q -> q.term(m -> m.field("_index").value(searchConfig.getTableName()))
		  );
		
		 // 过滤权限条件
		  if( !ObjectUtils.isEmpty(searchConfig.getDataRole()) ){
		      Map<String, List<SysPermissionDataRuleModel>> dataRoleMap = searchConfig.getDataRole();
		      ESDataPermissionUtil.addFilterRule(dataRoleMap,boolQuery);
		  }
		
		  for (String singlekeyword :keywordarry) {
		      if (StringUtils.hasText(singlekeyword.trim())) {
		          boolQuery.must(
		                  q -> q.multiMatch(multiMatchQueryBuilder -> multiMatchQueryBuilder
		                          .fields(fdnamelist).query(singlekeyword.trim()).operator(Operator.Or).analyzer("ik_smart"))
		          );
		      }
		  }
  }

}