# KQL syntax for serial difference/delta filter

**URL:** <https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100>\
**Category:** Kibana\
**Created:** [March 3, 2021, 12:10pm UTC](https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100 "2021-03-03T12:10:54Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Muhammad\_Faisal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/muhammad_faisal/32/79213_2.png) [@Muhammad\_Faisal](https://discuss.elastic.co/u/Muhammad_Faisal)\
**Post date:** [March 3, 2021, 12:10pm UTC](https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100/1 "2021-03-03T12:10:54Z")

</div>

hi guys,

my numeric data is coming aggregating , so i am using aggregation "sum of serial difference of values" to take delta...how do i put this delta in KQL filter in search box...  
example  
value1 : 20  
value 2: 50  
delta : 30

need KQL syntax to get results detla 30

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [March 3, 2021, 4:02pm UTC](https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100/2 "2021-03-03T16:02:27Z")

</div>

That's not what KQL is for. As documented, KQL is only capable of _filtering_ documents: [Kibana Query Language | Kibana Guide [7.11] | Elastic](https://www.elastic.co/guide/en/kibana/current/kuery-query.html)

---

<div class="post-metadata">

**Author:** ![Muhammad\_Faisal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/muhammad_faisal/32/79213_2.png) [@Muhammad\_Faisal](https://discuss.elastic.co/u/Muhammad_Faisal)\
**Post date:** [March 3, 2021, 8:33pm UTC](https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100/3 "2021-03-03T20:33:43Z")

</div>

thanks..then how do we put a filter in watcher JSON to get only those docs where "delta" is greater than particular value...

for example ,  
value 1: 50, doc:1  
value2 : 50, doc:2  
value 3: 100, doc : 3 - delta : 50 ,

need to set a filter in watcher alert when delta is greater than 0

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [March 4, 2021, 6:00pm UTC](https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100/4 "2021-03-04T18:00:31Z")

</div>

You will need to write an [Advanced Watcher](https://www.elastic.co/guide/en/kibana/current/watcher-ui.html#watcher-create-advanced-watch) using the query DSL. The `derivative` pipeline aggregation is the function you are looking for, which is an aggregation, not a query.

---

<div class="post-metadata">

**Author:** ![Muhammad\_Faisal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/muhammad_faisal/32/79213_2.png) [@Muhammad\_Faisal](https://discuss.elastic.co/u/Muhammad_Faisal)\
**Post date:** [March 5, 2021, 10:10am UTC](https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100/5 "2021-03-05T10:10:35Z")

</div>

hi ,

i am using below condition in advanced watcher

"condition" : {  
"script" : {  
"source" : "return (ctx.payload.aggregations.3.buckets.4.buckets.2.buckets.1-metric.value) \> params.threshold",  
"params" : {  
"threshold" : 0  
}  
}},

whereas my payload result in simulation output is..

i need to put a condition when 1-metric value is greater than zero , then trigger action ...but its giving exception for condition in painless...can you assist here how to put condition in painless

"result": {  
"execution\_time": "2021-03-05T10:07:55.927Z",  
"execution\_duration": 13,  
"input": {  
"type": "search",  
"status": "success",  
"payload": {  
"\_shards": {  
"total": 4,  
"failed": 0,  
"successful": 4,  
"skipped": 0  
},  
"hits": {  
"hits": ,  
"total": 124,  
"max\_score": null  
},  
"took": 11,  
"timed\_out": false,  
"aggregations": {  
"3": {  
"doc\_count\_error\_upper\_bound": 0,  
"sum\_other\_doc\_count": 0,  
"buckets": [  
{  
"4": {  
"doc\_count\_error\_upper\_bound": 0,  
"sum\_other\_doc\_count": 0,  
"buckets": [  
{  
"2": {  
"buckets": [  
{  
"key\_as\_string": "2021-03-02T11:00:00.000+03:00",  
"doc\_count": 1,  
"1-metric": {  
"value": 0  
},  
"key": 1614672000000  
}

---

<div class="post-metadata">

**Author:** ![Muhammad\_Faisal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/muhammad_faisal/32/79213_2.png) [@Muhammad\_Faisal](https://discuss.elastic.co/u/Muhammad_Faisal)\
**Post date:** [March 5, 2021, 1:35pm UTC](https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100/6 "2021-03-05T13:35:04Z")

</div>

i tried to use below loops but giving error

```
	"condition": {
  "script": {
    "source": """

```

for(int i = 0; i \< ctx.payload.aggregations.3.buckets.length; i++) {  
for(int j = 0; j \< ctx.payload.aggregations.3.buckets.4.buckets.length; j++) {  
for(int k = 0; k \< ctx.payload.aggregations.3.buckets.4.buckets.2.buckets.length; k++){  
if ( ['ctx.payload.aggregations.3.buckets.4.buckets.2.buckets.1-metric.value'] \> 1)  
return true;

```
}

```

}  
}

return false;  
"""

}}

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [March 5, 2021, 3:39pm UTC](https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100/7 "2021-03-05T15:39:55Z")

</div>

I'm sorry, but I am not able to help you with your painless scripts because I don't know how. We have a separate forum for discussion of watcher/alerting scripts and you should ask your question there: [Topics tagged stack-alerting](https://discuss.elastic.co/tags/c/elastic-stack/elasticsearch/6/stack-alerting)

---

<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:** [April 2, 2021, 3:40pm UTC](https://discuss.elastic.co/t/kql-syntax-for-serial-difference-delta-filter/266100/8 "2021-04-02T15:40:19Z")

</div>

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