Hello, I am an engineer preparing to migrate from splunk to elasticsearch.
I wanted to aggregate the highest value of count every minute, and I wanted to replace the empty part with zero.
However, it didn't work out as I thought.
The bottom is mapping.
{
"level2_null_time_included" : {
"mappings" : {
"_meta" : {
"created_by" : "ml-file-data-visualizer"
},
"properties" : {
"@timestamp" : {
"type" : "date"
},
"_time" : {
"type" : "date",
"format" : "iso8601"
},
"count" : {
"type" : "double"
},
"host" : {
"type" : "keyword"
},
"source" : {
"type" : "keyword"
},
"sourcetype" : {
"type" : "keyword"
}
}
}
}
}
Below is the _search syntax.
POST level2_null_time_included/_search
{
"size": 0,
"query": {
"bool": {
"filter": [
{
"range": {
"_time": {
"gte": "2019-09-11T20:00:00.000",
"lte": "2019-09-11T21:00:00.000",
"time_zone": "+09:00"
}
}
}
],
"must": [
{
"match": {
"sourcetype": "stash"
}
}
],
"must_not": [
{
"wildcard": {
"source": "*attack*"
}
}
]
}
},
"aggs": {
"date_histo": {
"date_histogram": {
"field": "_time",
"interval": "1m",
"format": "yyyy-MM-dd HH:mm:ss"
},
"aggs": {
"maxCntBySource": {
"terms": {
"field": "source",
"size": 1000000
},
"aggs": {
"maxCnt": {
"max": {
"field": "count",
"script": {
"lang": "painless",
"source": "Math.round(doc['count'].value*100)/100.00"
},
"missing": 0
}
}
}
}
}
}
}
}
Below is the result.
#! Deprecation: [interval] on [date_histogram] is deprecated, use [fixed_interval] or [calendar_interval] in the future.
{
"took" : 2,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 309,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"date_histo" : {
"buckets" : [
{
"key_as_string" : "2019-09-11 11:40:00",
"key" : 1568202000000,
"doc_count" : 27,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "P2_VXT02",
"doc_count" : 27,
"maxCnt" : {
"value" : -3.3
}
}
]
}
},
{
"key_as_string" : "2019-09-11 11:41:00",
"key" : 1568202060000,
"doc_count" : 60,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "P2_VXT02",
"doc_count" : 60,
"maxCnt" : {
"value" : -3.3
}
}
]
}
},
{
"key_as_string" : "2019-09-11 11:42:00",
"key" : 1568202120000,
"doc_count" : 60,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "P2_VXT02",
"doc_count" : 60,
"maxCnt" : {
"value" : -3.29
}
}
]
}
},
{
"key_as_string" : "2019-09-11 11:43:00",
"key" : 1568202180000,
"doc_count" : 60,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "P2_VXT02",
"doc_count" : 60,
"maxCnt" : {
"value" : -3.31
}
}
]
}
},
{
"key_as_string" : "2019-09-11 11:44:00",
"key" : 1568202240000,
"doc_count" : 60,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "P2_VXT02",
"doc_count" : 60,
"maxCnt" : {
"value" : -3.3
}
}
]
}
},
{
"key_as_string" : "2019-09-11 11:45:00",
"key" : 1568202300000,
"doc_count" : 41,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "P2_VXT02",
"doc_count" : 41,
"maxCnt" : {
"value" : -3.31
}
}
]
}
},
{
"key_as_string" : "2019-09-11 11:46:00",
"key" : 1568202360000,
"doc_count" : 0,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ ]
}
},
{
"key_as_string" : "2019-09-11 11:47:00",
"key" : 1568202420000,
"doc_count" : 0,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ ]
}
},
{
"key_as_string" : "2019-09-11 11:48:00",
"key" : 1568202480000,
"doc_count" : 0,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ ]
}
},
{
"key_as_string" : "2019-09-11 11:49:00",
"key" : 1568202540000,
"doc_count" : 0,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [ ]
}
},
{
"key_as_string" : "2019-09-11 11:50:00",
"key" : 1568202600000,
"doc_count" : 1,
"maxCntBySource" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "P2_VXT02",
"doc_count" : 1,
"maxCnt" : {
"value" : -3.37
}
}
]
}
}
]
}
}
}
Compare "2019-09-11 11:49:00" and "2019-09-11 11:50:00"
There's nothing in the bucket in front of it.
The latter has maxCnt in the bucket.
What I want to do is print out the buckets in the front so that they're zero. Not empty space.
What should I do?
Thank you for reading through.