# Kibana calculate Apdex with value from scripted\_field

**URL:** <https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845>\
**Category:** Kibana\
**Created:** [September 25, 2018, 2:05pm UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845 "2018-09-25T14:05:16Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![VincentNijhuis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vincentnijhuis/32/35860_2.png) [@VincentNijhuis](https://discuss.elastic.co/u/VincentNijhuis)\
**Post date:** [September 25, 2018, 2:05pm UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/1 "2018-09-25T14:05:16Z")

</div>

Hello.

Currently we maintain a cluster of 10 Elasticsearch nodes for our customer and we started experimenting with APM.  
The thing we are missing is a possibility to determine the Apdex score like New Relic describes: [https://docs.newrelic.com/docs/apm/new-relic-apm/apdex/apdex-measure-user-satisfaction](https://docs.newrelic.com/docs/apm/new-relic-apm/apdex/apdex-measure-user-satisfaction)

For an Apdex score in Kibana we tried a lot of things to create the same formula as New Relic uses.  
As of today i had the best idea ever, I thought 😋 .  
I created an scripted field in the APM index with the following:  
Field name: adpex\_score

```
double score;

if ( doc['transaction.duration.us'].empty ) {
    return '-';
} else {
    if ( doc['transaction.duration.us'].value < 1200 ) {
        score = 1;
    } else if ( doc['transaction.duration.us'].value > 1200 ) {
        if ( doc['transaction.duration.us'].value <= 4000 ) {
            score = 0.5;
        } else {
            score = 0;
        }
    }
}

return score;

```

What i want to do at this moment is create the formula in Kibana. But Kibana can't access the new scripted\_field values to calculate or count the total where apdex\_score == 1 or something.  
Also the visual builder is not possible to access the field at all and to me it looks like no visualisation is able to calculate the following formula.  
Apdex = (satisfied request (apdex\_score:1)) + (tolerating request (apdex\_score:0.5) /2) / total number of requests (count(apdex\_score)).

We get al the numbers in the bar graph when when clicking on Visualize in the discovery tab. Are we able to calculate with those values?

Am i missing something?

Also we think that we have al the right values when we collect a dataset where [transaction.duration.us](http://transaction.duration.us): exists

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![Bargs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bargs/32/5429_2.png) [@Bargs](https://discuss.elastic.co/u/Bargs)\
**Post date:** [September 25, 2018, 7:00pm UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/2 "2018-09-25T19:00:20Z")

</div>

> Kibana can't access the new scripted\_field values to calculate or count the total where apdex\_score == 1 or something.

It sounds like you're trying to use the scripted field in the query bar? The lucene query language doesn't support scripted fields, but you should still be able to create a filter on it. The filters are the little pills that appear below the query bar.

If you're using Kibana 6.0+ you can also try the [new query language](https://www.elastic.co/guide/en/kibana/current/kuery-query.html), which supports scripted fields.

> Also the visual builder is not possible to access the field at all and to me it looks like no visualisation is able to calculate the following formula.  
> Apdex = (satisfied request (apdex\_score:1)) + (tolerating request (apdex\_score:0.5) /2) / total number of requests (count(apdex\_score)).

You're correct, Time Series Visual Builder [doesn't currently support](https://github.com/elastic/kibana/issues/17100) scripted fields. Timelion supports them now though and it can do timeseries math so you might try that out. Otherwise you'll need to add those additional calculations to the scripted field itself.

---

<div class="post-metadata">

**Author:** ![VincentNijhuis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vincentnijhuis/32/35860_2.png) [@VincentNijhuis](https://discuss.elastic.co/u/VincentNijhuis)\
**Post date:** [September 26, 2018, 9:57am UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/3 "2018-09-26T09:57:38Z")

</div>

Thanks for your reply.

I was only using the query bar to test if the field was searchable. Also we use the new query bar only. This on is much more understandable for our customers. We use the latest software version at the moment: 6.4.1 and we are able to find the field with the filters. We can switch between filters.

What we want to achieve is count a total of apdex\_score where apdex\_score 1 and the same for 0.5. Whit these values we would be able to create the Apdex formula. Is there a way to count this total and put that in the scripted field?

---

<div class="post-metadata">

**Author:** ![Bargs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bargs/32/5429_2.png) [@Bargs](https://discuss.elastic.co/u/Bargs)\
**Post date:** [September 26, 2018, 6:31pm UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/4 "2018-09-26T18:31:17Z")

</div>

I see what you're asking now. Unfortunately I don't think that will be possible at the moment. I thought you'd at least be able to do it with timelion, but it turns out timelion only has limited support for scripted fields so far. I've reopened an issue for supporting them in the [timelion query language itself](https://github.com/elastic/kibana/issues/9022).

We're working on adding KQL support to timelion and TSVB, so that will be your best bet in the near term. I don't have a target version for you, but keep an eye on the release notes.

In core Kibana you'll need [support for bucket script aggs](https://github.com/elastic/kibana/issues/4707) before you can do what you want, but I suspect that'll be a longer wait than KQL support in TSVB and Timelion.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [September 26, 2018, 6:35pm UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/5 "2018-09-26T18:35:59Z")

</div>

Have you considered adding the adpex\_score as a field to each document at index time, e.g. using an ingest pipeline?

---

<div class="post-metadata">

**Author:** ![VincentNijhuis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vincentnijhuis/32/35860_2.png) [@VincentNijhuis](https://discuss.elastic.co/u/VincentNijhuis)\
**Post date:** [October 1, 2018, 11:24am UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/6 "2018-10-01T11:24:31Z")

</div>

Thanks for the pipeline hint. We are now adding the apdex\_score field in the pipeline for indexes with transatcion in its documents.  
The script has changed due to that:

```
"processors" : [
{
  "set" : {
    "field" : "transaction.apdex_score",
    "value" : "{{transaction.duration.us}}",
    "ignore_failure" : true
  },
  "convert" : {
    "field" : "transaction.apdex_score",
    "type" : "double"
  },
  "script" : {
    "lang" : "painless",
    "source" : "if ( ctx.transaction.apdex_score < 2500 ) { ctx.transaction.apdex_score = 1 } else if ( ctx.transaction.apdex_score > 2500 ) { if ( ctx.transaction.apdex_score <= 10000 ) { ctx.transaction.apdex_score = 0.5 } else { ctx.transaction.apdex_score = 0 } }"
  }
}

```

This now fully works as we need it to work. the mapping is set to create a apdex\_score field as double.  
Now the challange get the count of all the fields per value (0, 0.5 and 1) so we can create the formula.  
Apdex = (satisfied request (apdex\_score:1)) + (tolerating request (apdex\_score:0.5) /2) / total number of requests (count(apdex\_score))

any ideas?

i tried to use json input in the visualisation metric count. but this does not seem to work.  
`{ "script" : "doc['transaction.apdex_score'].value == 1" }`

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 1, 2018, 11:33am UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/7 "2018-10-01T11:33:36Z")

</div>

> [@VincentNijhuis](#):
>
> Apdex = (satisfied request (apdex\_score:1)) + (tolerating request (apdex\_score:0.5) /2) / total number of requests (count(apdex\_score))

Isn't the formula you provided basically a simple average of the `apdex_score` field, or am I missing something?

---

<div class="post-metadata">

**Author:** ![VincentNijhuis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vincentnijhuis/32/35860_2.png) [@VincentNijhuis](https://discuss.elastic.co/u/VincentNijhuis)\
**Post date:** [October 1, 2018, 2:16pm UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/8 "2018-10-01T14:16:36Z")

</div>

The are 3 possible values for this field, 0, 0.5 and 1.  
For the formula there is no need to use the field with value 0. But we need the total of all fields to determine the Apdex score. the fields with value 0 are part of the total of all fields.

for example when looking at 1 week.  
There might be 15000 documents in total with a valid apdex\_score.  
1500 times value 0  
6500 times 0.5  
7000 times 1  
the formula would be  
Apdex = 7000 + (6500 / 2) / 15000 = 0,683333333333333333333  
This is what is am trying to achieve, I hope this make sense 😉 .

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 1, 2018, 2:26pm UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/9 "2018-10-01T14:26:33Z")

</div>

The average for the `apdex_score` field would be `((7000 * 1) + (6500 * 0.5) + (1500 * 0)) / (7000 + 6500 + 1500) = 0.6833333333`

---

<div class="post-metadata">

**Author:** ![VincentNijhuis](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vincentnijhuis/32/35860_2.png) [@VincentNijhuis](https://discuss.elastic.co/u/VincentNijhuis)\
**Post date:** [October 2, 2018, 7:27am UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/10 "2018-10-02T07:27:44Z")

</div>

Ow wow i thought the average aggregations would make a different formula.  
So today i started with a very big facepalm after trying this with test data on my local machine

1500 times value 0  
6500 times 0.5  
7000 times 1

The average is is indeed working for me. Thank you very much for your patience and help.

---

<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:** [October 30, 2018, 7:27am UTC](https://discuss.elastic.co/t/kibana-calculate-apdex-with-value-from-scripted-field/149845/11 "2018-10-30T07:27:47Z")

</div>

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