Getting count using aggregation

Elastic Stack Elasticsearch
1

Hi Team,

{
  "query": {
    "bool": {
      "filter": [
        {
        "bool": {
          "should":{
          "range": {
            "@timestamp": {
              "gte": "now-15m"
            }
          }
          }
        }
        },
        {
          "bool": {
            "should": [
            {
              "match_phrase": {
                "ResponseCode": "005"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "008"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "081"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "091"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "096"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "900"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "009"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "0068"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "153"
              }
            }
          ]
          }
        }
      ]
    }
  }, 
  "aggs": {
    "types_count": {
      "value_count": {
        "field": "ResponseCode.keyword"
      }
    
    }
  }
}

This query give me the total count of those who has this values(which i specified) in response code

I want those count result who has this set of response code value which i specified plus all the response code. In other words can i get two different count values one for specific set of values and other one is all the values

2

Hi,

aggregation works on documents which meet the query. You have to use the following query instead.

{
  "query": {
    "range": {
            "@timestamp": {
              "gte": "now-15m"
            }
          }
  },
  "aggs": { ...

With this query, you can use filters aggregation to make two buckets: one for specific set of values and the other for all values for ResponseCode.

3

Thank you @Tomo_M for replying me. I used filters aggregation and getting error.
Error

{
  "error" : {
    "root_cause" : [
      {
        "type" : "parsing_exception",
        "reason" : "unknown query [query]",
        "line" : 76,
        "col" : 22
      }
    ],
    "type" : "parsing_exception",
    "reason" : "unknown query [query]",
    "line" : 76,
    "col" : 22,
    "caused_by" : {
      "type" : "named_object_not_found_exception",
      "reason" : "[76:22] unknown field [query]"
    }
  },
  "status" : 400
}

{
  "query": {
    "bool": {
      "filter": [
        { "range":
            {
              "@timestamp":
              {
                "gte": "2022-09-21T09:00:00.000Z",
              "lte": "2022-09-21T09:30:00.000Z",
              "format":"yyyy-MM-dd HH:mm:ss||strict_date_optional_time ||epoch_millis"
              }
            }
          }
      ]
    }
  }, 
  "aggs": {
    "messages": {
      "filters": {
        "filters": {
          "response_code":
          {
            "bool": {
               "should": [
            {
              "match_phrase": {
                "ResponseCode": "005"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "008"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "081"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "091"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "096"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "900"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "009"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "0068"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "153"
              }
            }
          ]
            }
          },
          "without_response":
          {
            "query": {
              "bool": {
                "must": [
                  {
                    "match_all": {}
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
}

But after sometime i made some changes i used other_bucket_key.

{
  "query": {
    "bool": {
      "filter": [
        { "range":
            {
              "@timestamp":
              {
                "gte": "2022-09-21T09:00:00.000Z",
              "lte": "2022-09-21T09:30:00.000Z",
              "format":"yyyy-MM-dd HH:mm:ss||strict_date_optional_time ||epoch_millis"
              }
            }
          }
      ]
    }
  }, 
  "aggs": {
    "messages": {
         "filters" : {
        "other_bucket_key": "other_messages",
        "filters": {
          "response_code":
          {
            "bool": {
               "should": [
            {
              "match_phrase": {
                "ResponseCode": "005"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "008"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "081"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "091"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "096"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "900"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "009"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "0068"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "153"
              }
            }
          ]
            }
          }
        }
      }
    }
  }
}

Result

"aggregations" : {
    "messages" : {
      "buckets" : {
        "response_code" : {
          "doc_count" : 906
        },
        "other_messages" : {
          "doc_count" : 98155
        }
      }
    }
  }

I want other_messages count 906+98155=99061 so bucket other_messages should be 99061. You can check whether i am doing wrong in query part because i got the error.

1 Like
4

Thank you for let me know the other_bucket solution.

As for your error message,

{
  "query": {
    "bool": {
      "filter": [
        { "range":
            {
              "@timestamp":
              {
                "gte": "2022-09-21T09:00:00.000Z",
              "lte": "2022-09-21T09:30:00.000Z",
              "format":"yyyy-MM-dd HH:mm:ss||strict_date_optional_time ||epoch_millis"
              }
            }
          }
      ]
    }
  }, 
  "aggs": {
    "messages": {
      "filters": {
        "filters": {
          "response_code":
          {
            "bool": {
               "should": [
            {
              "match_phrase": {
                "ResponseCode": "005"
              }
            },
            {
              "match_phrase": {
                "ResponseCode": "008"
              }
            },...
          ]
            }
          },
          "without_response":
          {
              "bool": {
                "must": [
                  {
                    "match_all": {}
                  }
                ]
              }
          }
        }
      }
    }
  }
}

will work.

Or, more simple without_response clause:

"without_response":
          {
            "match_all": {}
          }

will also work.

1 Like
5

Thank you @Tomo_M

1 Like
6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.