# Getting fields in ascending order from aggregation in watcher

**URL:** <https://discuss.elastic.co/t/getting-fields-in-ascending-order-from-aggregation-in-watcher/320013>\
**Category:** Kibana\
**Tags:** painless\
**Created:** [November 29, 2022, 8:46am UTC](https://discuss.elastic.co/t/getting-fields-in-ascending-order-from-aggregation-in-watcher/320013 "2022-11-29T08:46:19Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![kurdit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kurdit/32/54994_2.png) [@kurdit](https://discuss.elastic.co/u/kurdit)\
**Post date:** [November 29, 2022, 8:46am UTC](https://discuss.elastic.co/t/getting-fields-in-ascending-order-from-aggregation-in-watcher/320013/1 "2022-11-29T08:46:19Z")

</div>

hi

I have a watcher for tracking high request time on my website urls, with the following request:

```auto
{
  "trigger": {
    "schedule": {
      "interval": "2m"
    }
  },
  "input": {
    "search": {
      "request": {
        "search_type": "query_then_fetch",
        "indices": [
          "nifi-*"
        ],
        "rest_total_hits_as_int": true,
        "body": {
          "size": 0,
          "query": {
            "bool": {
              "must": [
                {
                  "range": {
                    "@timestamp": {
                      "gte": "now-2m",
                      "lte": "now"
                    }
                  }
                },
                {
                  "query_string": {
                    "analyze_wildcard": true,
                    "fields": ["hostnames"],
                    "query": "example.com"
                  }
                }
              ]
            }
          },
          "aggregations": {
            "hostnames": {
              "terms": {
                "field": "hostnames",
                "order": {
                  "request_time": "desc"
                }
              },
              "aggregations": {
                "request_time": {
                  "avg": {
                    "field": "request_time"
                  }
                },
                "avg_agg_pipe": {
                  "bucket_selector": {
                    "buckets_path": {
                      "request_time": "request_time"
                    },
                    "script": {
                      "lang": "expression",
                      "source": "request_time > 20"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  "condition": {
    "script": {
      "source": "return ctx.payload.aggregations.hostnames.buckets.size() > 0",
      "lang": "painless"
    }
  },
  "actions": {
  ......
"""
{{#ctx.payload.aggregations.hostnames.buckets}}
hostnames: {{key}}
Time: {{request_time.value}} second
{{/ctx.payload.aggregations.hostnames.buckets}}
"""
      }
    }
  },
  "transform": {
    "script": {
      "source": """for (bucket in ctx.payload.aggregations.hostnames.buckets) {bucket.request_time.value = Math.round(bucket.request_time.value);} return ['aggregations': ctx.payload.aggregations]""",
      "lang": "painless"
    }
  }
}

```

this is how it **returns the result:**

```auto
"result": {
    "execution_time": "2022-11-29T08:10:57.977Z",
    "execution_duration": 71,
    "input": {
      "type": "search",
      "status": "success",
      "payload": {
        "hits": {
          "hits": [],
          "total": 5744,
          "max_score": null
        },
        "took": 58,
        "timed_out": false,
        "aggregations": {
          "hostnames": {
            "doc_count_error_upper_bound": -1,
            "sum_other_doc_count": 5583,
            "buckets": [
              {
                "request_time": {
                  "value": 234
                },
                "doc_count": 4,
                "key": "test100.example.com"
              },
              {
                "request_time": {
                  "value": 15
                },
                "doc_count": 14,
                "key": "test11.example.com"
              },
              {
                "request_time": {
                  "value": 11
                },
                "doc_count": 11,
                "key": "test03.example.com"
              },
              {
                "request_time": {
                  "value": 8
                },
                "doc_count": 13,
                "key": "test21.example.com"
              }
            ]
          }
        }
      }

```

**structure** of my documents in index

```auto
"_source": {
    "event": {},
    "body-request": "test",
    "type": "nifi",
    "req-uri": "/mydata/service/303dbs",
    "request_time": 34.03
    "hostnames": "test100.example.com"
    ....
"_source": {
    "event": {},
    "body-request": "testtest",
    "type": "nifi",
    "req-uri": "/mydata/test",
    "request_time": 201.34
    "hostnames": "test100.example.com"
    ....
"_source": {
    "event": {},
    "type": "nifi",
    "req-uri": "/topic/forum",
    "request_time": 20.03
    "hostnames" : "test100.example.com"
    ....

.....

```

_ **and I have a question:** _  
how can I get for the "actions" section the top 5 "request\_time" and "req-uri" fields that have a maximum response time within the time requested by the watcher

like this:

> hostnames: [test100.example.com](http://test100.example.com)
> 
> URI: /mydata/test  
> Time: 201.34
> 
> URI: /mydata/service/303dbs  
> Time: 34.03
> 
> URI: /topic/forum  
> Time: 20.03  
> ...

---

<div class="post-metadata">

**Author:** ![kurdit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kurdit/32/54994_2.png) [@kurdit](https://discuss.elastic.co/u/kurdit)\
**Post date:** [December 1, 2022, 8:36am UTC](https://discuss.elastic.co/t/getting-fields-in-ascending-order-from-aggregation-in-watcher/320013/2 "2022-12-01T08:36:45Z")

</div>

friends, any ideas?

---

<div class="post-metadata">

**Author:** ![kurdit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kurdit/32/54994_2.png) [@kurdit](https://discuss.elastic.co/u/kurdit)\
**Post date:** [December 8, 2022, 9:51pm UTC](https://discuss.elastic.co/t/getting-fields-in-ascending-order-from-aggregation-in-watcher/320013/3 "2022-12-08T21:51:01Z")

</div>

I came up with a solution to the problem without the need to aggregate new fields with data

added to transform:

```auto
"transform": {
     script: {
       "source": " return ['sort': ctx.payload.hits.hits,]",
       "lang": "painless"
     }
   }

```

added to actions:

```auto
.....
{{#ctx.payload.sort}}
{{_source.request_time}} and {{_source.req_url}}
{{/ctx.payload.sort}}
.....

```

added to the aggregation:

```auto
"_source": [
             "req_url",
             "request_time"
           ]

```

added sorting to the body:

```auto
sort: [
             {
               "request_time": {
                 "order": "desc"
               }
             }
           ]

```

---

<div class="post-metadata">

**Author:** ![kurdit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kurdit/32/54994_2.png) [@kurdit](https://discuss.elastic.co/u/kurdit)\
**Post date:** [December 12, 2022, 7:58am UTC](https://discuss.elastic.co/t/getting-fields-in-ascending-order-from-aggregation-in-watcher/320013/4 "2022-12-12T07:58:56Z")

</div>

No, I was overjoyed ahead of time. this is not a solution. in this case it just returns a list of documents that have nothing to do with the aggregation

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 9, 2023, 7:59am UTC](https://discuss.elastic.co/t/getting-fields-in-ascending-order-from-aggregation-in-watcher/320013/5 "2023-01-09T07:59:25Z")

</div>

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