Metric visual for number occurrences of a field and apply arithmetic

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

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.

1 Like

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.

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

1 Like

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?

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

Are you still getting that same error?

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:

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

Many thanks.