How to do arithmetic operation on two elastic queries result

Hello,

I am drawing timeline link chart to get avg post counts per active users. I have following query result:
A: total active users [unique] count per hour
B. total posts added by all users per hour
I want to draw line chart for avg posts per active users for that hour [B/A].

How I can divide result of B by A for each hour.

I am able to get those avg directly from elasticsearch, it means it is supported by elasticsearch.
Below is sample query:

GET post-analytics/_search

{
“size”: 0,
“aggs”: {
“hour”: {
“date_histogram”: {
“field”: “ts”,
“interval”: “hour”
},
“aggs”: {
“posts”: {
“filter”: {
“terms”: {
“act”: [
“addpost”,
“resharepost”
]
}
},
“aggs”: {
“activities”: {
“value_count”: {
“field”: “act”
}
}
}
},
“users”: {
“cardinality”: {
“field”: “uid”
}
},
“percentage”: {
“bucket_script”: {
“buckets_path”: {
“totalPosts”: “posts>activities”,
“activeUsers”: “users”
},
“script”: “params.totalPosts / params.activeUsers”
}
}
}
}
}
}
I am not sure, How I can achieve the same in Grafana.

elasticsearch does not support nested queries or arithmetic on whole query results

I am able to get those avg directly from elasticsearch, it means it is supported by elasticsearch.
Below is sample query:

GET post-analytics/_search
{
“size”: 0,
“aggs”: {
“hour”: {
“date_histogram”: {
“field”: “ts”,
“interval”: “hour”
},
“aggs”: {
“posts”: {
“filter”: {
“terms”: {
“act”: [
“addpost”,
“resharepost”
]
}
},
“aggs”: {
“activities”: {
“value_count”: {
“field”: “act”
}
}
}
},
“users”: {
“cardinality”: {
“field”: “uid”
}
},
“percentage”: {
“bucket_script”: {
“buckets_path”: {
“totalPosts”: “posts>activities”,
“activeUsers”: “users”
},
“script”: “params.totalPosts / params.activeUsers”
}
}
}
}
}
}
I am not sure, How I can achieve the same in Grafana.

Do you have paid support to enable this feature or implement required customization??

there is an open feature request to support bucket script aggregations

1 Like

I was wondering will this feature be available soon? Will it be in the next release?

1 Like