Hi all
I am looking to count the number of unique referer values over a specified time period (range) so that I can compare that to the count of referer values within a 15 minute interval using a bucket aggregation. I thought cumulative cardinality would give me one consistent value for the specified time period (in the below example I would have expected the cumulative cardinality would be a consistent value of 6 for each bucket aggregation) but when I use this, the cumulative cardinality changes for each bucket. See below what I am running in dev tools and the outcome. Is there any suggestions of what I could use instead?
GET kibana_sample_data_logs/_search
{
"query": {
"bool": {
"filter": [
{
"match_phrase": {
"event.dataset": "sample_web_logs"
}
},
{
"match_phrase": {
"geo.dest": "CN"
}
},
{
"range": {
"@timestamp": {
"gte": "2022-04-17T05:00:00.000Z",
"lte": "2022-04-17T09:00:00.000Z",
"format": "strict_date_optional_time||epoch_millis"
}
}
}
],
"should": [],
"must_not": []
}
},
"aggs": {
"geo_dest": {
"terms": {
"field": "geo.dest",
"size": 100
},
"aggs": {
"per_15m": {
"date_histogram": {
"field": "@timestamp",
"fixed_interval": "15m"
},
"aggs": {
"referer_count": {
"cardinality": {
"field": "referer"
}
},
"card": {
"cumulative_cardinality": {
"buckets_path": "referer_count"
}
},
"referer_count_bucket_filter": {
"bucket_selector": {
"buckets_path": {
"Referers": "referer_count",
"Cards": "card"
},
"script": "params.Referers > 0"
}
},
"referer_names": {
"terms": {
"field": "referer",
"size": 100
}
}
}
},
"bucket_existing_bucket_filter": {
"bucket_selector": {
"buckets_path": {
"count": "per_15m._bucket_count"
},
"script": "params.count>0"
}
}
}
}
}
}
Output in dev tools;
{
"took" : 54,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 7,
"relation" : "eq"
},
"max_score" : 0.0,
"hits" : [
{
"_index" : "kibana_sample_data_logs",
"_id" : "2SScYYABcT1nYR_CjeLB",
"_score" : 0.0,
"_source" : {
"agent" : "Mozilla/5.0 (X11; Linux i686) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.696.50 Safari/534.24",
"bytes" : 9888,
"clientip" : "138.172.136.98",
"extension" : "deb",
"geo" : {
"srcdest" : "US:CN",
"src" : "US",
"dest" : "CN",
"coordinates" : {
"lat" : 30.77883333,
"lon" : -86.52211111
}
},
"host" : "artifacts.elastic.co",
"index" : "kibana_sample_data_logs",
"ip" : "138.172.136.98",
"machine" : {
"ram" : 10737418240,
"os" : "osx"
},
"memory" : null,
"message" : "138.172.136.98 - - [2018-07-22T06:16:49.806Z] "GET /beats/metricbeat/metricbeat-6.3.2-amd64.deb_1 HTTP/1.1" 200 9888 "-" "Mozilla/5.0 (X11; Linux i686) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.696.50 Safari/534.24"",
"phpmemory" : null,
"referer" : "http://www.elastic-elastic-elastic.com/success/lloyd-hammond",
"request" : "/beats/metricbeat/metricbeat-6.3.2-amd64.deb",
"response" : 200,
"tags" : [
"success",
"info"
],
"timestamp" : "2022-04-17T06:16:49.806Z",
"url" : "https://artifacts.elastic.co/downloads/beats/metricbeat/metricbeat-6.3.2-amd64.deb_1",
"utc_time" : "2022-04-17T06:16:49.806Z",
"event" : {
"dataset" : "sample_web_logs"
}
}
},
{
"_index" : "kibana_sample_data_logs",
"_id" : "BSScYYABcT1nYR_CjePB",
"_score" : 0.0,
"_source" : {
"agent" : "Mozilla/5.0 (X11; Linux i686) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.696.50 Safari/534.24",
"bytes" : 1778,
"clientip" : "117.46.30.183",
"extension" : "deb",
"geo" : {
"srcdest" : "US:CN",
"src" : "US",
"dest" : "CN",
"coordinates" : {
"lat" : 41.93887417,
"lon" : -72.68322833
}
},
"host" : "artifacts.elastic.co",
"index" : "kibana_sample_data_logs",
"ip" : "117.46.30.183",
"machine" : {
"ram" : 5368709120,
"os" : "osx"
},
"memory" : null,
"message" : "117.46.30.183 - - [2018-07-22T07:23:08.209Z] "GET /apm-server/apm-server-6.3.2-amd64.deb HTTP/1.1" 200 1778 "-" "Mozilla/5.0 (X11; Linux i686) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.696.50 Safari/534.24"",
"phpmemory" : null,
"referer" : "http://facebook.com/warning/yuri-shargin",
"request" : "/apm-server/apm-server-6.3.2-amd64.deb",
"response" : 200,
"tags" : [
"success",
"info"
],
"timestamp" : "2022-04-17T07:23:08.209Z",
"url" : "https://artifacts.elastic.co/downloads/apm-server/apm-server-6.3.2-amd64.deb",
"utc_time" : "2022-04-17T07:23:08.209Z",
"event" : {
"dataset" : "sample_web_logs"
}
}
},
{
"_index" : "kibana_sample_data_logs",
"_id" : "YiScYYABcT1nYR_CjePB",
"_score" : 0.0,
"_source" : {
"agent" : "Mozilla/5.0 (X11; Linux i686) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.696.50 Safari/534.24",
"bytes" : 9888,
"clientip" : "138.172.136.98",
"extension" : "deb",
"geo" : {
"srcdest" : "US:CN",
"src" : "US",
"dest" : "CN",
"coordinates" : {
"lat" : 30.77883333,
"lon" : -86.52211111
}
},
"host" : "artifacts.elastic.co",
"index" : "kibana_sample_data_logs",
"ip" : "138.172.136.98",
"machine" : {
"ram" : 10737418240,
"os" : "osx"
},
"memory" : null,
"message" : "138.172.136.98 - - [2018-07-22T06:16:49.806Z] "GET /beats/metricbeat/metricbeat-6.3.2-amd64.deb HTTP/1.1" 200 9888 "-" "Mozilla/5.0 (X11; Linux i686) AppleWebKit/534.24 (KHTML, like Gecko) Chrome/11.0.696.50 Safari/534.24"",
"phpmemory" : null,
"referer" : "http://www.elastic-elastic-elastic.com/success/lloyd-hammond",
"request" : "/beats/metricbeat/metricbeat-6.3.2-amd64.deb",
"response" : 200,
"tags" : [
"success",
"info"
],
"timestamp" : "2022-04-17T06:16:49.806Z",
"url" : "https://artifacts.elastic.co/downloads/beats/metricbeat/metricbeat-6.3.2-amd64.deb",
"utc_time" : "2022-04-17T06:16:49.806Z",
"event" : {
"dataset" : "sample_web_logs"
}
}
},
{
"_index" : "kibana_sample_data_logs",
"_id" : "ZCScYYABcT1nYR_CjePB",
"_score" : 0.0,
"_source" : {
"agent" : "Mozilla/5.0 (X11; Linux x86_64; rv:6.0a1) Gecko/20110421 Firefox/6.0a1",
"bytes" : 0,
"clientip" : "168.207.246.199",
"extension" : "",
"geo" : {
"srcdest" : "US:CN",
"src" : "US",
"dest" : "CN",
"coordinates" : {
"lat" : 39.63885556,
"lon" : -90.77843111
}
},
"host" : "www.elastic.co",
"index" : "kibana_sample_data_logs",
"ip" : "168.207.246.199",
"machine" : {
"ram" : 9663676416,
"os" : "win 7"
},
"memory" : null,
"message" : "168.207.246.199 - - [2018-07-22T08:54:08.276Z] "GET /enterprise HTTP/1.1" 503 0 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:6.0a1) Gecko/20110421 Firefox/6.0a1"",
"phpmemory" : null,
"referer" : "http://www.elastic-elastic-elastic.com/success/jing-haipeng",
"request" : "/enterprise",
"response" : 503,
"tags" : [
"success",
"info"
],
"timestamp" : "2022-04-17T08:54:08.276Z",
"url" : "Install Elastic Cloud Enterprise | Elastic",
"utc_time" : "2022-04-17T08:54:08.276Z",
"event" : {
"dataset" : "sample_web_logs"
}
}
},
{
"_index" : "kibana_sample_data_logs",
"_id" : "ciScYYABcT1nYR_CjePB",
"_score" : 0.0,
"_source" : {
"agent" : "Mozilla/5.0 (X11; Linux x86_64; rv:6.0a1) Gecko/20110421 Firefox/6.0a1",
"bytes" : 6817,
"clientip" : "214.190.64.93",
"extension" : "zip",
"geo" : {
"srcdest" : "US:CN",
"src" : "US",
"dest" : "CN",
"coordinates" : {
"lat" : 41.05221889,
"lon" : -93.68966222
}
},
"host" : "artifacts.elastic.co",
"index" : "kibana_sample_data_logs",
"ip" : "214.190.64.93",
"machine" : {
"ram" : 2147483648,
"os" : "win 8"
},
"memory" : null,
"message" : "214.190.64.93 - - [2018-07-22T08:46:33.307Z] "GET /kibana/kibana-6.3.2-windows-x86_64.zip HTTP/1.1" 200 6817 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:6.0a1) Gecko/20110421 Firefox/6.0a1"",
"phpmemory" : null,
"referer" : "http://www.elastic-elastic-elastic.com/success/grigori-nelyubov",
"request" : "/kibana/kibana-6.3.2-windows-x86_64.zip",
"response" : 200,
"tags" : [
"success",
"info"
],
"timestamp" : "2022-04-17T08:46:33.307Z",
"url" : "https://artifacts.elastic.co/downloads/kibana/kibana-6.3.2-windows-x86_64.zip",
"utc_time" : "2022-04-17T08:46:33.307Z",
"event" : {
"dataset" : "sample_web_logs"
}
}
},
{
"_index" : "kibana_sample_data_logs",
"_id" : "eSScYYABcT1nYR_CjePB",
"_score" : 0.0,
"_source" : {
"agent" : "Mozilla/5.0 (X11; Linux x86_64; rv:6.0a1) Gecko/20110421 Firefox/6.0a1",
"bytes" : 7085,
"clientip" : "11.4.76.252",
"extension" : "",
"geo" : {
"srcdest" : "US:CN",
"src" : "US",
"dest" : "CN",
"coordinates" : {
"lat" : 34.65447222,
"lon" : -112.4195833
}
},
"host" : "www.elastic.co",
"index" : "kibana_sample_data_logs",
"ip" : "11.4.76.252",
"machine" : {
"ram" : 16106127360,
"os" : "osx"
},
"memory" : null,
"message" : "11.4.76.252 - - [2018-07-22T08:30:47.577Z] "GET /Elasticsearch HTTP/1.1" 200 7085 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:6.0a1) Gecko/20110421 Firefox/6.0a1"",
"phpmemory" : null,
"referer" : "http://www.elastic-elastic-elastic.com/success/michel-tognini",
"request" : "/Elasticsearch",
"response" : 200,
"tags" : [
"success",
"security"
],
"timestamp" : "2022-04-17T08:30:47.577Z",
"url" : "Download Elasticsearch | Elastic",
"utc_time" : "2022-04-17T08:30:47.577Z",
"event" : {
"dataset" : "sample_web_logs"
}
}
},
{
"_index" : "kibana_sample_data_logs",
"_id" : "qyScYYABcT1nYR_CjePC",
"_score" : 0.0,
"_source" : {
"agent" : "Mozilla/5.0 (X11; Linux x86_64; rv:6.0a1) Gecko/20110421 Firefox/6.0a1",
"bytes" : 9659,
"clientip" : "1.5.239.89",
"extension" : "deb",
"geo" : {
"srcdest" : "US:CN",
"src" : "US",
"dest" : "CN",
"coordinates" : {
"lat" : 30.78602056,
"lon" : -89.50450694
}
},
"host" : "artifacts.elastic.co",
"index" : "kibana_sample_data_logs",
"ip" : "1.5.239.89",
"machine" : {
"ram" : 12884901888,
"os" : "ios"
},
"memory" : null,
"message" : "1.5.239.89 - - [2018-07-22T07:41:41.377Z] "GET /Elasticsearch/Elasticsearch-6.3.2.deb HTTP/1.1" 200 9659 "-" "Mozilla/5.0 (X11; Linux x86_64; rv:6.0a1) Gecko/20110421 Firefox/6.0a1"",
"phpmemory" : null,
"referer" : "http://facebook.com/success/david-leestma",
"request" : "/Elasticsearch/Elasticsearch-6.3.2.deb",
"response" : 200,
"tags" : [
"error",
"security"
],
"timestamp" : "2022-04-17T07:41:41.377Z",
"url" : "https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.3.2.deb",
"utc_time" : "2022-04-17T07:41:41.377Z",
"event" : {
"dataset" : "sample_web_logs"
}
}
}
]
},
"aggregations" : {
"geo_dest" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "CN",
"doc_count" : 7,
"per_15m" : {
"buckets" : [
{
"key_as_string" : "2022-04-17T06:15:00.000Z",
"key" : 1650176100000,
"doc_count" : 2,
"referer_names" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "http://www.elastic-elastic-elastic.com/success/lloyd-hammond",
"doc_count" : 2
}
]
},
"referer_count" : {
"value" : 1
},
"card" : {
"value" : 1
}
},
{
"key_as_string" : "2022-04-17T07:15:00.000Z",
"key" : 1650179700000,
"doc_count" : 1,
"referer_names" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "http://facebook.com/warning/yuri-shargin",
"doc_count" : 1
}
]
},
"referer_count" : {
"value" : 1
},
"card" : {
"value" : 2
}
},
{
"key_as_string" : "2022-04-17T07:30:00.000Z",
"key" : 1650180600000,
"doc_count" : 1,
"referer_names" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "http://facebook.com/success/david-leestma",
"doc_count" : 1
}
]
},
"referer_count" : {
"value" : 1
},
"card" : {
"value" : 3
}
},
{
"key_as_string" : "2022-04-17T08:30:00.000Z",
"key" : 1650184200000,
"doc_count" : 1,
"referer_names" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "http://www.elastic-elastic-elastic.com/success/michel-tognini",
"doc_count" : 1
}
]
},
"referer_count" : {
"value" : 1
},
"card" : {
"value" : 4
}
},
{
"key_as_string" : "2022-04-17T08:45:00.000Z",
"key" : 1650185100000,
"doc_count" : 2,
"referer_names" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "http://www.elastic-elastic-elastic.com/success/grigori-nelyubov",
"doc_count" : 1
},
{
"key" : "http://www.elastic-elastic-elastic.com/success/jing-haipeng",
"doc_count" : 1
}
]
},
"referer_count" : {
"value" : 2
},
"card" : {
"value" : 6
}
}
]
}
}
]
}
}
}