Calculation on ES query?

Elastic Stack Elasticsearch
1

Hi,

until now I was mostly just using kibana and i borrowed some queries from there, to fire some of them via curl.
So I am quite a Newbie about quering ES directly. So I need your help.

I have an index which has documents with following fields:

  • userName
  • sessionId
  • processingTime
  • serviceCall

Now I would like to query elasticsearch for following result:

(count of documents) / (count of unique userName).
(count of documents) / (count of unique sessionId)

I managed to query the following

GET /tux-prod-2017.08.30/_search
{
   "size": 0,
   "aggs": {
    "uniqueUsers": {
      "cardinality": {
        "field": "userName.keyword"
      }
    },
    "uniqueSessions": {
      "cardinality": {
        "field": "sessionId.keyword"
      }
    }
   },
    "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "analyze_wildcard": true,
            "query": "type.keyword: useractionlog"
          }
        },
        {
          "range": {
            "@timestamp": {
              "gte": "now-10m/m",
              "lte": "now/m"
            }
          }
        }
      ],
      "must_not": []
    }
  }
}

As result i get the metrics which I need as Input for my calculation

{
  "took": 7,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "failed": 0
  },
  "hits": {
    "total": 18291,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "uniqueUsers": {
      "value": 644
    },
    "uniqueSessions": {
      "value": 4929
    }
  }
}

So I could extract the uniqueUser.value, uniqueSessions.value and hits.total and calculate externally.

But is there a way to get it calculated inside ES?

Thanks, Andreas

2

Yes, you can use Bucket Script Aggregation

3

Tanks for the fast reply, but seems I don' t get it, because it does not work yet :frowning:

In first step I would like to calculate (sum of processing time) / (unique user count)

I wrote following request:

GET /tux-prod-2017.08.30/_search
{
    "size": 0,
    "aggs":
    {
      "per 10 minutes" : 
		  {
        "date_histogram" : 
  			{
          "field" : "@timestamp",
          "interval" : "10m"
        },
        "aggs": 
        {
          "uniqueUsers": 
          {
            "cardinality": 
            {
              "field": "userName.keyword"
            }
          },
          "procTimeSum":
          {
            "sum": {
              "field": "processingTime"
            }
          },
          "myscript":
          {
            "bucket_script": 
            {
              "buckets_path":
              {
                "uniqueUserCount": "uniqueUsers",
                "procTime": "procTimeSum"
              },
              "script": "procTime / uniqueUserCount"
            }
          }
        } 
		  }
   },
    "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "analyze_wildcard": true,
            "query": "type.keyword: useractionlog"
          }
        },
        {
          "range": {
            "@timestamp": {
              "gte": "now-30m/m",
              "lte": "now/m"
            }
          }
        }
      ],
      "must_not": []
    }
  }
}

I get follwoing response:

{
  "error": {
    "root_cause": [],
    "type": "reduce_search_phase_exception",
    "reason": "[reduce] ",
    "phase": "merge",
    "grouped": true,
    "failed_shards": [],
    "caused_by": {
      "type": "script_exception",
      "reason": "compile error",
      "caused_by": {
        "type": "illegal_argument_exception",
        "reason": "Variable [procTime] is not defined."
      },
      "script_stack": [
        "procTime / uniqueUserCoun ...",
        "^---- HERE"
      ],
      "script": "procTime / uniqueUserCount",
      "lang": "painless"
    }
  },
  "status": 503
}

Where is my mistake?
I also tried to set the bucket path to

"procTime": "procTimeSum>sum"
as I understood the example, but then I get following response:

{
  "error": {
"root_cause": [
  {
    "type": "illegal_argument_exception",
    "reason": "No aggregation [sum] found for path [procTimeSum>sum]"
  }
],
"type": "search_phase_execution_exception",
"reason": "all shards failed",
"phase": "query_fetch",
"grouped": true,
"failed_shards": [
  {
    "shard": 0,
    "index": "tux-prod-2017.08.30",
    "node": "ZQ5zjwZdSJeant4L4mkS5A",
    "reason": {
      "type": "illegal_argument_exception",
      "reason": "No aggregation [sum] found for path [procTimeSum>sum]"
    }
  }
],
"caused_by": {
  "type": "illegal_argument_exception",
  "reason": "No aggregation [sum] found for path [procTimeSum>sum]"
}
  },
  "status": 400
}
4

Try with

"script": "params.procTime / params.uniqueUserCount"
5

thanks, the params. did it.

6

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