Data table visualization with custom aggregation query

[hjSohn]

Hello Team,

I am using ES to check chatbot's response success rate. (document contains message and tag)

I want to visualize that

• total count : the number of documents that contain @send in customTags keyword field
• failed count : the number of documents that contain @fail in customTags keyword field
• success count : (total count - failed count)

So I composed a query like below :

 {
  "query": {
    "bool": {
      "filter": [
        {
          "match": {
            "sender": 10000
          }
        }
      ]
    }
  },
  "aggs": {
    "report": {
      "date_histogram": {
        "field": "@timestamp",
        "interval": "60m"
      },
      "aggs": {
        "send": {
          "filter": {
            "match": {"customTags": "@send"}
          },
          "aggs": {
            "count": {
              "sum": {
                "field": "sender"
              }
            }
          }
        },
        "fail": {
          "filter": {
            "match": {"customTags": "@fail"}
          },
          "aggs": {
            "count": {
              "sum": {
                "field": "sender"
              }
            }
          }
        },
        "success": {
          "bucket_script": {
            "buckets_path": {
              "total": "send > count",
              "fail": "fail > count"
            },
            "script": "(params.total - params.fail)/10000"
          }
        }
      }
    }
  }
}

As you can see, "send" and "fail" aggregations contain "sum aggregation" for calculation.
So my questions are

1. Is it fine to use query like that to calculate with results?
2. Could I setup this query using visualization UI? I tried many times but didn't find good one.

For the reference I attach the response of query below.

{
    "took": 22,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 125105,
        "max_score": 0,
        "hits": []
    },
    "aggregations": {
        "whole_result": {
            "buckets": [
                {
                    "key_as_string": "2018-06-26T15:00:00.000Z",
                    "key": 1530025200000,
                    "doc_count": 4294,
                    "fail": {
                        "doc_count": 86,
                        "count": {
                            "value": 8600
                        }
                    },
                    "send": {
                        "doc_count": 173,
                        "count": {
                            "value": 17300
                        }
                    },
                    "success": {
                        "value": 87
                    }
                },
...

Thanks for help in advance!

[tylersmalley]

Reaching out to the visualization team regarding this.

[tylersmalley]

Actually, I looked into this. Here is my sample data:

DELETE /discuss-138637

PUT /discuss-138637
{
  "mappings": {
    "_doc": {
      "properties": {
        "@timestamp": {
          "type": "date"
        },
        "customTags": {
          "type": "keyword"
        }
      }
    }
  }
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:00:00",
  "customTags": ["@send"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:01:00",
  "customTags": ["@fail"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:02:00",
  "customTags": ["@send"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:03:00",
  "customTags": ["@send"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:04:00",
  "customTags": ["@send"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:05:00",
  "customTags": ["@fail"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:06:00",
  "customTags": ["@send"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:07:00",
  "customTags": ["@send"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:08:00",
  "customTags": ["@fail"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:09:00",
  "customTags": ["@send"]
}

POST /discuss-138637/_doc
{
  "@timestamp": "2018-07-06T00:10:00",
  "customTags": ["@send"]
}

Let me know if this is not what you're looking for . But I believe there are a few ways to do this.

The first is with filters. This is ok if you know all the possible values:

The second is with an aggregation. Note you will need to select "Show total" under options.

[hjSohn]

Hello @tylersmalley,

I'm really appreciate about the answer but my original question is about calculation with result. You show me that N(all) = N(@send) + N(@fail) but how about this? N(something) = N(@send) - N(@fail). I just want to visualize the data through subtraction. Could it be possible?

Please help me again~!