Sum of derivated data by particular fieald

[Ludek]

Hi there,
I have data from a lot of servers and need to summarize. All data are counting up, so I need derivate by hostname. After that make sum.

My sample data
Time hostname actions.add header
[time] mx1 29
[time] mx2 25
[time] mx3 25
[time] mx4 27
[time] mx1 26
[time] mx2 25
[time] mx3 25
[time] mx4 26
[time] mx1 24
[time] mx2 25
[time] mx3 25
[time] mx4 25

I'd like to do this:
Max 'actions.add header' group by hostname -> derivate -> sum from all servers

Any idea how to do this in Kibana?

[Ludek]

I thought that, this is it, but doesn't work

[jbudz (Jon Budzenski)]

This looks exactly right to me, can you check your developer tools for the response from /metrics/vis/data? If the error is anything related to a null pointer exception I'm thinking it's a bug, https://github.com/elastic/elasticsearch/issues/27544.

[Ludek]

When I try to use your example, I have this message only when I have a small number of samples in db. I increase it for test and it was OK. But, when I extend the query more close to what I need (meens derivative function) it returns me the same message:

{
  "error": {
    "root_cause": [],
    "type": "search_phase_execution_exception",
    "reason": "",
    "phase": "fetch",
    "grouped": true,
    "failed_shards": [],
    "caused_by": {
      "type": "null_pointer_exception",
      "reason": null
    }
  },
  "status": 503
}

My query is:

GET index-*/_search    
{
  "size": 1,
  "query": {
    "range": {
      "date": {
        "gte": "now-15m/m",
        "lte": "now"
      }
    }
  },
  "aggs": {
    "timeseries": {
      "date_histogram": {
        "field": "date",
        "interval": "10s"
      },
      "aggs": {
        "maxRJ": {
          "max": {
            "field": "actions.reject"
          }
        },
        "calculation": {
          "bucket_script": {
            "buckets_path": {
              "maxRJ": "maxRJ"
            }, 
            "script": { 
              "source": "params.maxRJ" 
            }
          }
        },
        "derive": {
          "derivative": {
            "buckets_path": "calculation"
          }
        },
        "cumsum": {
          "cumulative_sum": {
            "buckets_path": "derive"
          }
        }
      }
    }
  }
}

[jbudz (Jon Budzenski)]

Can you share your Elasticsearch version? Is upgrading to 6.4 an option? It looks like there was a fix merged then.

[Ludek]

I'm on 5.6.11. And for now, it's not possible to upgrade to 6.X. We are planning it in future, but it's about 1 year or more.

[jbudz (Jon Budzenski)]

Okay, as far as I can tell our best course is to plot this outside of TSVB then. Timelion is an option, and will crunch derivative and cumulative sum in the Kibana server avoiding the elasticsearch bug.

a query would look something like:

.es(metric=max:actions.add_header, split=hostname:10).derivative().cusum()

[Ludek]

Had no time to play with this. But I thought about Timelion before. OK, thanks for help. I'll use Timelion.

[Ludek]

I have partial success.
.es(index=spam-status-*, timefield=date, metric="max:actions.add header",split=hostname:20).derivative()

returns this graph

But when I use cusum() function, it not makes sense what I get for me.
.es(index=spam-status-*, timefield=date, metric="max:actions.add header",split=hostname:20).derivative().cusum()

I expected the sum of all derivative series.