请求优化Metricbeat的ElasticSearch查询：按小时统计最常用进程

要实现按小时维度拆分进程分组统计，你需要把**日期直方图聚合（date_histogram）**作为最外层聚合，基于@timestamp按小时划分，然后在其中嵌套你原来的进程启动时间和名称聚合。这样就能得到每个小时段内的进程启动时间分组，以及每个启动时间对应的进程名称统计。

以下是修改后的完整查询语句：

GET metricbeat*/_search
{
  "query": {
    "bool": {
      "must": [
        { "wildcard": { "beat.hostname": "ibmcx*" }},
        { "range": { "@timestamp": { "gte": "2019-03-22T00:00:00", "lte": "2019-03-23T00:00:00" }}},
        { "terms": { "beat.hostname": ["ibmcxapp101", "ibmcxapp102", "ibmcxapp103", "ibmcxapp104", "ibmcxapp105", "ibmcxapp106", "ibmcxapp107", "ibmcxapp108", "ibmcxapp109", "ibmcxapp110", "ibmcxapp111", "ibmcxapp112", "ibmcxapp113", "ibmcxapp114", "ibmcxapp115", "ibmcxapp116", "ibmcxapp117", "ibmcxapp118", "ibmcxapp119", "ibmcxapp120", "ibmcxapp121", "ibmcxapp122", "ibmcxxaa100", "ibmcxxaa101", "ibmcxxaa102", "ibmcxxaa103", "ibmcxxaa104", "ibmcxxaa105", "ibmcxxaa106", "ibmcxxaa107", "ibmcxxaa108", "ibmcxxaa109", "ibmcxxaa110", "ibmcxxaa111", "ibmcxxaa112", "ibmcxxaa201", "ibmcxxaa202", "ibmcxxaa203", "ibmcxxaa204"] }},
        { "exists": { "field": "system.process.cmdline" }}
      ],
      "must_not": [
        { "term": { "system.process.username": "NT AUTHORITY\\SYSTEM" }},
        { "term": { "system.process.username": "NT AUTHORITY\\NETWORK SERVICE" }},
        { "term": { "system.process.username": "NT AUTHORITY\\LOCAL SERVICE" }},
        { "term": { "system.process.username": "NT AUTHORITY\\Servicio de red" }},
        { "term": { "system.process.username": "" }}
      ]
    }
  },
  "size": 0,
  "aggs": {
    "group_by_hour": {
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "hour",
        "format": "yyyy-MM-dd HH:mm:ss"
      },
      "aggs": {
        "group_by_start_time": {
          "terms": {
            "field": "system.process.cpu.start_time",
            "order": { "_key": "asc" }
          },
          "aggs": {
            "group_by_name": {
              "terms": {
                "field": "system.process.name.keyword",
                "order": { "_count": "desc" }
              }
            }
          }
        }
      }
    }
  }
}

关键修改说明： #

• 最外层添加group_by_hour聚合：使用date_histogram按@timestamp每小时拆分结果，calendar_interval: "hour"确保按自然小时划分，format参数让时间桶的可读性更好。
• 嵌套原有聚合：把你原来的group_by_start_time和group_by_name聚合放到group_by_hour的aggs下，这样每个小时桶内都会包含对应时段的进程启动时间和名称统计。
• 优化排序：在group_by_start_time里添加了order: { "_key": "asc" }保证启动时间升序排列，在group_by_name里用order: { "_count": "desc" }让每个启动时间下的进程按出现次数从多到少排序，方便快速找到最常用的进程。
• 清理冗余配置：移除了原查询中重复的size:0和不必要的sort字段，因为聚合查询的size:0只需要设置一次，而排序逻辑已经整合到聚合的order参数中。

如果需要调整时间间隔（比如按30分钟），只需要修改calendar_interval的值为"30m"即可。

内容的提问来源于stack exchange，提问作者Juan Montoya