Rollup with max/min aggregation on @timestamp field typed as "float"

[ISim (Ivan Š.)]

Hello all,

I have a index with accounting events with @timestamp (type "date"). There is a high volume of events so I created rollup job (elasticsearch 7.17.3) with

              "date_histogram" : {
                "delay" : "2h",
                "field" : "@timestamp",
                "time_zone" : "Europe/Prague",
                "calendar_interval" : "1d"
              },

with key terms (session, IP address,...) and required metrics (transfered data,...).

Because I need to know when the session started and finished exactly, I added metrics min and max for @timestamp field. The idea was to query elastic with

GET /rollup-sessions-daily/_search
{
  "size": 0, 
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "Session-Id.terms.value": "2eb6d2c49e32b7f18edbb4de6f3d960f"
          }
        }
      ]
    }
  },
  "aggs": {
    "startedAt": { "min": {"field": "@timestamp.min.value"}},
    "lastEventAt": {"max": {"field": "@timestamp.max.value"}}
  }
}

to get the exact time of the first and last session occurrence. Sessions sometimes run over days or weeks, sometimes start and finish the same day.

This approach works except one thing: min and max field are typed as float in rollup index This is a little bit inconvenient.

The rollup job API created mapping:

      "properties" : {
        "@timestamp" : {
          "properties" : {
            "date_histogram" : {
              "properties" : {
                "_count" : {
                  "type" : "long"
                },
                "interval" : {
                  "type" : "keyword"
                },
                "time_zone" : {
                  "type" : "keyword"
                },
                "timestamp" : {
                  "type" : "date"
                }
              }
            },
            "max" : {
              "properties" : {
                "value" : {
                  "type" : "float"
                }
              }
            },
            "min" : {
              "properties" : {
                "value" : {
                  "type" : "float"
                }
              }
            }
          }
        },

Is there any solution to keep @timestamp.min.value and @timestamp.max.value as date?

I also run into a problem with field typed as "ip". Although I can aggregate query using "terms" with this field

GET /events-*/_search
{
  "size": 0,
  "aggs": {
    "IP": {
      "terms": {
        "field": "IP-Address"
      }
    }
  }
}

the type "ip" is not allowed for rollup aggregations. It is described in documentation "The terms group can be used on keyword or numeric fields to allow bucketing via the terms aggregation at a later point"). Of course, I read this statement later then I needed....

I tried to create transformation job (pivoted index) - the same terms and metrics and created mapping is correct for @timestamp.max, @timestamp.min and even "ip" type is not an issue for aggregation.

     "properties" : {
        "@timestamp" : {
          "properties" : {
            "max" : {
              "type" : "date"
            },
            "min" : {
              "type" : "date"
            }
          }
        },
        "IP-Address" : {
          "type" : "ip"
        },
...

But running transform job is time and resource expensive for large set of events.

Thanks in advance for any hint.
Ivan

[warkolm (Mark Walkom)]

You should setup an index template to do that for you, as the resultant rollup index is the same as a normal one.

[ISim (Ivan Š.)]

Hello Mark,
thank you for your reply. I created template for rollup index as you mentioned above but I got my log flooded with:

org.elasticsearch.index.mapper.MapperParsingException: failed to parse field [@timestamp.max.value] of type [date] in document with id 'isp-rollup-radacct-sessions-daily-2$-korUCu3MF6sbSA1eOFwSg'. Preview of field's value: '1.65
1599427E12'
        at org.elasticsearch.index.mapper.FieldMapper.parse(FieldMapper.java:255) ~[elasticsearch-7.17.3.jar:7.17.3]
[...cut...]
Caused by: java.lang.IllegalArgumentException: failed to parse date field [1.651599427E12] with format [strict_date_optional_time||epoch_millis]

Well, it looks the float value could not be parsed with "epoch_milis" format of date type. So I created pipeline

{
  "processors": [
    {
      "convert": {
        "field": "@timestamp.max.value",
        "type": "long",
        "on_failure": [
          {
            "set": {
              "description": "Set 'error.message'",
              "field": "error.message",
              "value": "Field @timestamp.max.value conversion error",
              "override": false
            }
          },
          {
            "set": {
              "field": "@timestamp.max.value",
               "value": 0
            }
          }
        ]
      }
    },
    {
      "convert": {
        "field": "@timestamp.min.value",
        "type": "long",
        "on_failure": [
          {
            "set": {
              "description": "Set 'error.message'",
              "field": "error.message",
              "value": "Field @timestamp.min.value conversion error",
              "override": false
            }
          },
          {
            "set": {
              "field": "@timestamp.min.value",
               "value": 0
            }
          }
        ]
      }
    }
  ]
}

and set the pipeline as default index pipeline.

Again, I failed - the indexed document looks like (_source field has more field but I left only important fields here):

     {
        "_index" : "index-name",
        "_type" : "_doc",
        "_id" : "index-name-v4$AFnuE_v_Bc5BuJSwfF0oMQ",
        "_score" : 1.0,
        "_ignored" : [
          "@timestamp.max.value",
          "@timestamp.min.value"
        ],
        "_source" : {
          "@timestamp.date_histogram.time_zone" : "Europe/Prague",
          "@timestamp.max.value" : 1.650318963E12,
          "error" : {
            "message" : "Field @timestamp.max.value conversion error"
          },
          "Acct-Input-Packets.max.value" : 3988737.0,
          "@timestamp.date_histogram._count" : 134,
          "Acct-Unique-Session-Id.terms._count" : 134,
          "Acct-Unique-Session-Id.terms.value" : "5464f1a2a9d35d482f7e2d50166ce23d",
          "@timestamp.date_histogram.interval" : "1d",
          "@timestamp" : {
            "min" : {
              "value" : 0
            },
            "max" : {
              "value" : 0
            }
          },
          "_rollup.version" : 2        
        }
      },

The pipeline processor couldn't reach field with name "@timestamp.max.value" because expects object, not "flat" field with dots. Am I right?

Again, thank you for any hint.

Ivan

[ISim (Ivan Š.)]

So, I currently use runtime fields to convert float values of @timestamp.min and @timestamp.max values to date type fields:

PUT /rollup-sessions-daily/_mappings
{
  "runtime": {
    "@timestamp.first_seen": {
      "type": "date",
      "script": """
          emit((long)doc['@timestamp.min.value'].value);
          """
    },
        "@timestamp.last_seen": {
      "type": "date",
      "script": """
          emit((long)doc['@timestamp.max.value'].value);
          """
    }
  }
}