Metric visual for number occurrences of a field and apply arithmetic

[Jeremy_Colton]

Hi,

I have read https://www.elastic.co/blog/kibana-4-beta-3-now-more-filtery and https://qbox.io/blog/elasticsearch-scripting-aggregations-2 but I cannot see how to use Kibana to fulfill my use-case:

I log an "event" field that contains various String values. I want to find the number of occurrences of a specific value "button clicked" and then multiply it by a monetary amount to calculate revenue and show this as a Metric visual.

Eg number of occurrences of event:"button clicked" * 0.25

How can i do this?

Many thanks

[spalger (Spencer Alger)]

One way you could do this is with a scripted field. That field might be clickValueInUSD, which has a field formatter for currency and a script value like doc['event'].values.includes('button clicked') ? 0.25 : 0 and then create a metric aggregation on the sum of that field.

[Jeremy_Colton]

Great!

I am running Kibana 4.5

I tried adding this scripted field exactly as you provided. Please see the attached screenshot.

When I run a metric 'sum' visual on this scripted field, Kibana complains with:

Error: Request to Elasticsearch failed: {"error":{"root_cause":[{"type":"script_exception","reason":"Failed to parse expression: doc['event'].values.includes('button clicked') ? (0.25) : 0"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":0,"index":"logstash-2016.05.21","node":"y35NyWVvSMy_bDDwfUVj7w","reason":{"type":"script_exception","reason":"Failed to compile inline script [doc['event'].values.includes('button clicked') ? (0.25) : 0] using lang [expression]","caused_by":{"type":"script_exception","reason":"Failed to parse expression: doc['event'].values.includes('button clicked') ? (0.25) : 0","caused_by":{"type":"parse_exception","reason":"unexpected character ''' on line (1) position (29)","caused_by":{"type":"lexer_no_viable_alt_exception","reason":null}}}}}]}}

Googling this hasn't revealed any solution other than to ask what scripting language is this? Do I need to enable something in Kibana to allow this script to run?

Many thanks.

[warkolm (Mark Walkom)]

You will need to enable it in ES - https://www.elastic.co/guide/en/elasticsearch/reference/2.3/modules-scripting.html#enable-dynamic-scripting

[Jeremy_Colton]

Hi @warkolm, I read the link you provided and allegedly enabled scripting by adding to my /etc/elasticsearch/elasticsearch.yml:

script.inline: true
script.indexed: true

I restarted my ES server. I deleted/re-added my script but I still get the same parse error as I reported above when adding my Metric visual. How can I know that scripting is enabled in the elasticsearch logs?

[Jeremy_Colton]

Hi @warkolm, can you be more specific about how to enable scripting since the link you provided didn't help. Is there a specific language I need to enable as well? When I restart ES, should the logs say that scripting for a given language is enabled?

Many thanks

[warkolm (Mark Walkom)]

Are you still getting that same error?

[Jeremy_Colton]

Yes...the link that you provided didn't help.

Ok, I discovered the problem. Your syntax was wrong. You can't use a String in a Scripted Field, only numbers! I found this link that explains it:

Calling groovy script from Kibana

Hi Darpan, You can use Groovy scripts within Kibana, but you have to use some of its advanced features to effectively make it do what you want. However, the way to do it depends on where the script needs to go. Can you write the query that you want to be processed? { "query" : { "filtered" : { "script" : { "script" : "your-script-name" } } } } If that's the case, then you can actually supply the inner-query portion in the Kibana search box: [image] { "filtere…

It's not simple to set up but the steps are there that explain how to do maths on a metric field.

Many thanks.