Use script in aggregation

I think im almost there so hopefully this should be an easy fix, im trying to get a percentage value

Percentage of docs that meet the filtered_docs criteria out of all the docs in the index.

Calculation should be - (total(from filter)/ total)*100

The query im trying to do gets me my values but I don't know how to complete the calculation with a script

GET <index>/_search
{
  "aggs": {
    "total": { "value_count": { "field": "_id" } },
    "filtered": {
      "filter": {
        "bool": {
          "must": [
            {
              "wildcard": {
                "field1": {
                  "value": "*<string>*"
                }
              }
            },
            {
              "range": {
                "field2": {
                  "lt": "now-30d/d"
                }
              }
            }
          ],
          "must_not": {
            "range": {
              "field3": {
                "gt": "now"
              }
            }
          }
        }
      },
      "aggs": {
        "total": {
          "value_count": { "field": "_id" }
        }
      }
    }
  }
}

I get the below result which is correct but I want to use a script of some kind in this aggregation to do (params.filtered.total / params.total) * 100

"aggregations" : {
    "total" : {
      "value" : 200
    },
    "filtered_docs" : {
      "meta" : { },
      "doc_count" : 20,
      "total" : {
        "value" : 20
      }
    }
  }

Happy to clarify any questions

Are you attempting to visualize the results in Kibana? If so, I would recommend the TSVB filter ratio function which can perform this calculation. Another alternative is Vega for custom queries. You can compare the various editors that we have to learn more.

If you are not trying to visualize the results in Kibana, you can use a bucket script. However Kibana does not support this.

Thanks @wylie thanks for the assistance,

Yes i am trying to visualise it in kibana but running into an issue, I was originally trying to use the filter ratio in TSVB but the filter would reduce the results and not give me the value i want.

I ONLY want to filter for the Numerator not the denominator, and Lucene does not allow me to do date maths such as the gt/lt statements. (I am using 7.9 and cannot upgrade)

"field3": {"gt": "now"} 
OR 
"field2": {"lt": "now-30d/d"}

I cannot set a global filter or my Denominator will also only be the filtered results and not the total number of files in the index.

I hope that makes sense.

Instead I am trying to use Vega but need to get the actual calculation working before I can start the visualisation *(total(from filter)/ total)100 which is where im struggling on the syntax.

If you could assist with that i would be grateful. Cheers

  1. I don't believe datemath is supported in Lucene, so like I've previously mentioned to you your best option in 7.9 is Vega.

  2. You need to use a bucket script aggregation, and the very first code example on the page has an example.

Follow up on the question

  • Wasn't able to upgrade so made do with a simplified version as a pie chart, updated the date field to be the date isn't invalid and just did field > now/d

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