Kibana calculate Apdex with value from scripted_field

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

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 :yum: .
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: exists

Thanks in advance.

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, 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 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.

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?

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.

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 before you can do what you want, but I suspect that'll be a longer wait than KQL support in TSVB and Timelion.

1 Like

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

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" }

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

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 :wink: .

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

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.

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