CODEWITHSUNDEEP
elasticsearchlucenekibana
jonny
jonny

Reputation: 177

Dividing counts of two different queries in kibana

I am trying to create a lucene expression for displaying division on counts of two queries. Both queries contain textual information and both results are in message field. I am not sure how to write this correctly. So far what i have done is without any luck -

doc['message'].value/doc['message'].value

for first query message contain text as - "404 not found"

for second query message contain text as - "500 error"

what i want to do is count(404 not found)/count(500 error)

I would appreciate any help.

Upvotes: 1

Views: 2423

Answers (1)

Miek
Miek

Reputation: 1228

I'm going to add the disclaimer that it would be significantly cleaner to just run two separate counts and perform the calculation on the client side like this:

GET /INDEX/_search
{
  "size": 0, 
  "aggs": {
    "types": {
      "terms": {
        "field": "type",
        "size": 10
      }
    }
  }
}

Which would return something like (except using your distinct keys instead of the types in my example):

  "aggregations": {
    "types": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "Article",
          "doc_count": 881
        },
        {
          "key": "Page",
          "doc_count": 301
        }
      ]
    }

Using that, take your distinct counts and calculated the average.

With the above being stated, here is the hacky way I was able to put together from (via single request) this

GET /INDEX/_search
{
  "size": 0,
  "aggs": {
    "parent_agg": {
      "terms": {
        "script": "'This approach is a weird hack'"
      },
      "aggs": {
        "four_oh_fours": {
          "filter": {
            "term": {
              "message": "404 not found"
            }
          },
          "aggs": {
            "count": {
              "value_count": {
                "field": "_index"
              }
            }
          }
        },
        "five_hundreds": {
          "filter": {
            "term": {
              "message": "500 error"
            }
          },
          "aggs": {
            "count": {
              "value_count": {
                "field": "_index"
              }
            }
          }
        },
        "404s_over_500s": {
          "bucket_script": {
            "buckets_path": {
              "four_oh_fours": "four_oh_fours.count",
              "five_hundreds": "five_hundreds.count"
            },
            "script": "return params.four_oh_fours / (params.five_hundreds == 0 ? 1: params.five_hundreds)"
          }
        }
      }
    }
  }
}

This should return an aggregate value based on the calculation within the script.

If someone can offer an approach aside from these two, I would love to see it. Hope this helps.

Edit - Same script done via "expression" type rather than painless (default). Just replace the above script value with the following:

        "script": {
          "inline": "four_oh_fours / (five_hundreds == 0 ? 1 : five_hundreds)",
          "lang": "expression"
        }

Updated the script here to accomplish the same thing via Lucene expressions

Upvotes: 3

Related Questions