Kibana: Support Scripted Metrics Aggregation

[doubret]

Replaced original description ~ @timroes

This ticket tracks implementing Elasticsearch's Scripted Metrics Aggregation (SMA) into Kibana.

SMA can be used to completely calculate a custom metric value based upon map/reduce on each individual document, i.e. you have the chance to map each document to a value (based on all fields in that document) and then use a combine and reduce script to reduce all those values into one metric result. An example for a scripted metric aggregation (taken from the documentation) could look as follows:

"scripted_metric": {
  "init_script" : "params._agg.transactions = []",
  "map_script" : "params._agg.transactions.add(doc.type.value == 'sale' ? doc.amount.value : -1 * doc.amount.value)", 
  "combine_script" : "double profit = 0; for (t in params._agg.transactions) { profit += t } return profit",
  "reduce_script" : "double profit = 0; for (a in params._aggs) { profit += a } return profit"
}

Do not confuse with Bucket script aggregation!

If you are simply looking into, e.g. the ratio of two metric values, this is not the right ticket to track. To calculate the result of multiple metric aggregation into a new value per bucket (e.g. metric1 / metric2 * 100), you would use the Bucket Script Aggregation available in Elasticsearch.

The progress for supporting that aggregation in Kibana is tracked in #4707.

Original description

Hi,

Are you planning to support scripted metric aggregation in a near future ?
I would like this to compute a ratio between to aggregations.
The first aggegation is NbClicks, the second is NbPrints, and i would like to output the ratio NbPrints / NbClicks.

With a scripted metric aggregation it would be easy to do, but if you know a way to do it without let me know please.

Thanks.

[rashidkpc]

Would be a great enhancement. I'd love to see a better script debugger in Kibana to complement this.

[doubret]

Should be simple to implement considered that it is somewhat similar to scripted fields (at least in terms of interface).

[timroes]

Would also like to see this a lot!

[prachaquant]

+1. I also want this.

[loren]

This would be huge. I just got the scripted metric aggregation working in Sense (yay!) and I'd love to be able to hook it up in Kibana for the win.

[monotek]

+1

[the-fine]

+1

[rashidkpc]

Unfortunately, due to the Groovy issues, we don't really have a secure way to accomplish this until we have a safe language that has loops and such

[hsm3]

I need this. Note that with many other similar products (NewRelic, Librato), you can submit a metric along with a "count" of how many samples that metric covers, and then the tool can do proper averages over a bucket of those items.

[leoatavalancha]

+1

[kzarzycki-advertine]

+1!

[lcs777]

+1

[gauravkumar37]

+1
I am storing pre-aggregated data in ES- sum() and count(), and want to calculate average.
Individual average can be calulated by scripts by dividing sum by count.
However, to see global average, I would need, sum(sum())/sum(count()) which I currently cannot do without scripted metrics aggregation.

[sqpdln]

+1

[ryaanwells]

+1
Is this something we can do in the front end in Kibana? Say if I'm processing the two columns doubret has said (NbPrints and NbClicks) then we already have the data we require.

Maybe the introduction of a new "Metrics" aggregation which is a "Ratio" of two current aggregations that have been processed by ES? Or could be two new specified aggregations (field and aggregation type) that we would aggregate in ES behind the scenes and then surface the ratio metric.

[jlew-cs]

@rashidkpc Regarding the groovy security issues:
Would it be possible to sidestep the problem by using file-based (or id-based) scripted aggregations (installed in ES config/scripts) rather than issuing dynamic script aggregation requests? This seems safe, and better than nothing.

[guybartal]

+1

[fabiangebert]

+1

[zijian1981]

+1

[vikrim1]

+1

[mimes70]

+1

[fabiangebert]

Can anyone give instructions how to implement this? My expectation would be that you can save scripted aggs in the objects list and use them from the dropdown in the visualize function.

[Krever]

+1

[jbarata]

+1

[nambrot]

+1

[fabiangebert]

I started here with a very basic implementation of scripted_metric in the visualize editor, hope that helps:
https://github.com/fabiangebert/kibana

[sbyim]

+1

[socifi]

+1

[tinexw]

+1

[rafael-munoz]

+1

[fabiangebert]

please give me some feedback on my draft: https://github.com/fabiangebert/kibana - feel free to suggest enhancements

[dessaya]

+1

[jailbirt]

Hi @fabiangebert , We downloaded your snapshot and We get it running after applies minor fixes (css and bower installation).

I can confirm that It's working. as expected using elasticsearch 1.5
Thanks!.

[davmrtl]

+1

[tombray]

+1 and thanks @fabiangebert -- that's exactly what I needed

[lokeshhctm]

Can you describe what changes you did exactly in CSS and bower installation?
I am getting error while "npm install".

npm ERR! 404 Not Found
npm ERR! not ok code 0

[rguillet]

+1

[gauravkumar37]

Hi @jailbirt Could you please describe the fixes you did with bower installation?

[Krever]

@fabiangebert I have checked your changes and it works perfectly well, thanks. I think you should make a pull request.

[JPriya2]

+1

[gairom]

+1

[OnBloom]

+1

[prashanttct07]

I am also looking for the same , and here my use case is as below:

I have data as : Id Cost Value 1 200 5000 1 120 1000 3 500 12600
1 350 18000

So here I want Output as avg for ID 1, (Sum Of cost/Sum of value)

So here , I am expecting O/P as (200+120+350)/(5000+1000+18000) not (200/5000) + (120/1000) + (350/18000).

So kindly let me know how we can achieve the same in Kibana

[fabiangebert]

@Prashant-Pal yes you can with scripted metrics.
I'll package my patch as a plugin for kibana 4.2 once the docs are ready

[prashanttct07]

@fabiangebert
Tnanks for your response. Could you please let me know if you have any estimated date when it can be available. Also a I checked https://github.com/fabiangebert/kibana

So how exactly I need to integrate it on my server running with ELK

[fabiangebert]

@Prashant-Pal you can just check out my 4.1 branch and use it straight away. I don't know when 4.2 docs will be available so I can make a plugin.

[prashanttct07]

@fabiangebert
Hi I am actually not getting like what all to be taken from the branch 4.1 , and where to put the same.

Considering I have ELK (Elasticsearch - 1.7.2, Kibana 4) setup so how I can achieve my purpose using this code and what all files I need to put up and where if you have any document.

[tero]

+1

[novozhenets]

+1

[pjancarik]

+1

[gimesketvirtadieni]

Hi

I was able to run Kibana from your REPO with scripted metric aggregation changes. However as @jailbirt wrote, CSS are not accessible from the explorer (404 error) so it is not possible to try this functionality out. Could you share a tip how to make CSS available from the explorer?

Thank you in advance!

[fabiangebert]

Hey guys, I've rebased my 4.1 branch onto 4.1.3 at https://github.com/fabiangebert/kibana. You don't have to add / remove any CSS files. We build the package straight from source. If you want to try it out locally, refer to the official contributing docs in the source.

[xeeper]

@gimesketvirtadieni I got the same error, after running npm run test && npm run build the css were created. I didn't have PhantomJS so the tests failed, but the css were there and so I could npm start successfully.

[xeeper]

BTW thanks @fabiangebert works great :+1:

[xeeper]

I've applied @fabiangebert changes on Kibana 4.3 (which supports ElaticSearch 2.0) at https://github.com/adience-code/kibana

[krnkzrn]

Hi All, a lot of issues are closed with link to this one and there is no progress on this question at all!
Any estimates/ideas on when scripted_metric will be supported in Kibana?

[JeremyColton]

Also interested in scripted aggregation of occurences of text fields so ratios can be calculated eg click-thru-rate from total click events/total events...is this in the works?

@navatm I have Kibana 4.4.0 so I have your updates that allow metrix aggregations? I can't see your updates in the release notes...

[sammy05]

+1

[stolendog]

+1

[mrvincenzo]

+1

[Aludirk]

+1

[robinwang08]

+1

[kiakaku]

+1

[SeanSnyders]

+1

[praveenuchiha]

+1

[satheeshravirintern]

+1

[asjp]

+1

[fbaligand]

+1

[medboz]

+1

[deeptiantony]

@fabiangebert Hello, I was trying to integrate scripted metric in kibana and came across https://github.com/fabiangebert/kibana. Could you please help me out on how to work on it?
Also it there any patch for Kibana 5.1.1? Thanks in advance

[fabiangebert]

@deeptiantony my kibana fork is based on Kibana 4.x, it won't work with 5.1.1. And I didn't work that out, yet. We're using Kibana 4.6 and have removed scripted metrics for now due to the discussion how it will be supported in the future.

[Stan-He]

+1

[cyy0523xc]

+1

[SolomonShorser-OICR]

:+1:

[lfroment-datasweet]

Hi everyone!
we might have an answer here https://github.com/datasweet-fr/kibana-datasweet-formula

For the time being, we are compatible with 5.6 and upward.
Enjoy!

[ensslen]

+1, particularly with elastic cloud not supporting Kibana Plugins.

[didinyah]

+1

[anomaliyao]

+1

[cumulate]

+1, is there any alternative to introduce this feature through customization or a Kibana plugin. Please provide us any input. I need this badly!!

[fbaligand]

@cummilate
If you look 4 comments above, there is a Kibana plugin that provides similar feature to scripted aggregation.

[cumulate]

@fbaligand
Yh realized that post commenting.

[georgezoto]

@lfroment-datasweet you saved the day! This is exactly what I was looking for. Thank you for supporting the newest version of Kibana 6.2.4! Merci Beaucoup 🇫🇷
Hope to see it as a new feature in Kibana itself.

[lfroment-datasweet]

@fbaligand , thanks Fabien for your message.
@georgezoto , your welcomed!

We had no contact with the Elastic team so far to integrate it as a standard feature of Kibana. I like the idea.

Regards

[timroes]

Just to solve some confusion here maybe. What that plugin does is not similar to scripted metric aggregations, but to bucket scripts, i.e. using the output of other metric aggregations in some formular to calculate a new value per bucket. The support for this is tracked in #4707 and will most likely be implemented way before scripted metrics aggregation.

Scripted metric aggregations are actually a way to completely script a metric by using map/reduce on a per document level.

But I admit, that this ticket got a bit mixed up, reading through the comments, so I changed the description of this ticket to make clear that it's about scripted metric aggregation and distinguish it from the Bucket Script aggregation (which as it looks is what most people, that are following here are actually looking for).

[timroes]

@lfroment-datasweet Do you want to open a PR against master adding your plugin to the known plugin list under "Others"? https://github.com/elastic/kibana/blob/master/docs/plugins/known-plugins.asciidoc

[lfroment-datasweet]

Hi @timroes. Thanks for your message.
It has already been done. It appears in the master list. As far as we understand, we have to wait for you to release the next 6.X version so that it appears in the latest version list.

regards

[lfroment-datasweet]

@timroes . You are absolutely right. Formula works on the output dataset given by ES and is computed "within" Kibana.

[timroes]

@lfroment-datasweet It seems we have forgotten to backport that entry. I am now backporting it to 6.x and 6.3, so it will appear on the list of known plugins from 6.3 onwards.

[fbaligand]

You're right @timroes, datasweet-formula brings feature way more similar to bucket script aggregation.

[georgezoto]

@lfroment-datasweet happy to some more progress. I noticed an issue with visualizations created in datasweet when displayed in kibana_dashboard_only_user mode though. I have opened an issue in datasweet's repository. It is breaking my dashboard currently.

Also are there any plans to support more chart types like region maps or pie charts through your plugin? This might be a current limitation of Kibana that does not allow multiple metrics to be defined for these kind of charts but I might be mistaken.

[tr4l]

+1

If I'm correct, this should be the same request to _msearch than agregating by terms, with the term field optional when we have a script inside the "JSON Input" Field.

The terms aggregration generate something like:

   "aggs":{
      "2":{
         "terms":{
            "field":"data.field.keyword",
            "size":10,
            "order":{
               "_count":"desc"
            },{
            "script":{
               "lang":"painless",
               "source":"myComplexFormulaThatIdontWantToUpdateDocumentWith"
            }
         }}
      }
   }

Which doesn't work (and should not work)
However, if I manually edit the request to remove the field terms (and replace it with the script)

"aggs":{  
      "2":{  
         "terms":{ 
           "size":10,
            "script":{  
               "lang":"painless",
               "source":"myComplexFormulaThatIdontWantToUpdateDocumentWith"
            }
         }
      }
   }

In that case the request to _msearch works.

As this is not supported yet, did someone have alternative (plugin ? manual edition of Vizualisation?) for this?
To add a piece of context, the formula will change regularly based on some experiment, and I really don't want to update my document each time.

[fbaligand]

@tr4l

Today, there are 2 alternatives :

• kibana-enhanced-table plugin for “table” visualizations
• kibana-datasweet-formula plugin for other visualisations (it works also for table visualizations)

[tr4l]

@tr4l

Today, there are 2 alternatives :

• kibana-enhanced-table plugin for “table” visualizations
• kibana-datasweet-formula plugin for other visualisations (it works also for table visualizations)

This look like both plugins works with the result provide by ES, when what I need is to agregate based on the result of my script.

Let say I have a Person Document, with First Name and Last Name.
I want to know the top most common Full Name using an aggregation with a script that concatenate both of them.
Of course I can update my document and add a FullName field, but that's not my goal.

[fbaligand]

Well, to do that in Kibana, the easiest way is to create a Kibana Scripted Field (in Management) named "full_name", which script is :
doc['first_name'].value + ' ' + doc['last_name'].value

Then you create a visualization (say Tag Cloud), and you do a "Terms" bucket aggregation based on "full_name" field.

[mathewthekkekara]

definitly a +1

[SylvainVISSIERE-GUERINET]

+1

[silviodc]

Hi Everyone,

There is any plans to include it on Kibana for the next release?
It seems very demanded in the community and the issue dates from 2015!

[anelson-vidscale]

I was able to connect the output of my scripted metric to a Vega visualization.
You might find that approach will unblock you and allow you to visualize the output from the scripted_metric.

In other news, I hear that supporting triple quoted strings is coming. I can't wait to push readable code into code reviews.

[yinrong]

+1

[Sagesh]

+1

[hendrikmuhs]

Another alternative for this is to use a transform, transforms are available since 7.2 under basic license.

1. create a transform that does the scripted metric aggregation
2. query the _output_ of the transform and create visualization, etc.

The benefit of transform is less load at query time and likely a more responsive dashboard. However, the price tag is the extra storage you need for the output index of the transform.

[fbaligand]

Nice alternative @hendrikmuhs

[SolomonShorser-OICR]

@hendrikmuhs According to the documentation for 7.5, transforms are "in beta and [are] subject to change" (https://www.elastic.co/guide/en/elasticsearch/reference/7.5/transform-overview.html). They're also marked as an X-pack feature. Do you know how stable transforms are? Have they changed much since being introduced in 7.2?

[hendrikmuhs]

@SolomonShorser-OICR Note that I am one of the authors of transform. It is an X-pack feature, while starting with 6.3 X-pack has been opened and elasticsearch distributions bundle it. We now like to say it's a commercial feature. In the case of transform however it's licensed as 'basic', which is a free license. That means you can use transform for free without any limitation.

Improvements have been made in every version, soon 7.6 will add cross cluster search support. Prior 7.5. the name was data frame transform.

I (and my co-workers) happily answer more questions, however I think this issue is not the right place. I only wanted to give a quick pointer to anyone trying to solve a usecase that require visualization of scripted metric results.

Let's use https://discuss.elastic.co/ for further questions (my name there: @Hendrik_Muhs)

[timroes]

Elasticsearch decided to discourage usage of scripted metric aggregations across the stack (see https://github.com/elastic/elasticsearch/issues/63096 for more details why). So we'll be closing this issue, since there are no longer plans to implement this.

Please don't confuse scripted metric aggregation with bucket script aggregation which can be used to do calculations, like average(bytes) / requests and which is tracked via #4707.