Model data points with multiple timestamps

Elastic Stack Kibana
1

Hey!
I have a question regarding modelling, and/or searching, data with multiple timestamps. Structure of the data:
The data is a list of licenses, each with two timestamps: Start date (starts) and End date (ends).
During this time period a license is considered "Active".

I would like to do two things. First:
I would like to for a given time period (e.g. 2018-05-30 TO 2018-06-05) count the amount of Active licenses. I have figured out this is semi doable (?) with the json queries but I would like to have it as a metric visualisation. The following json query

{
  "query": {
    "bool": {
      "must": {
        "range": {
          "starts" : {
              "lte" : "2018-05-30 00:00:00"
            }
        }
      },
      "filter": {
        "range": {
          "ends": {
            "gte" : "2018-05-30 00:00:00"
          }
        }
      }
    }
  }
}

Will give me the correct amount of active licenses for that specific date. But again, I would like to have it as a metric visualisation and also be dependant on the time range of the dashboard. I.e.
STARTS lte Dashboard ENDTIME
AND
ENDS gte Dashboard STARTTIME

Secondly:
I would like to model these amounts over time in a time series. I.e. for each time period (days, weeks etc.) model the amount of licenses in a time series.

2

You can just use a query_string query to make a filter for documents with start times above X date and end times below Y date. Example:

starts:[2017-01-01 TO *] AND ends:[* TO 2017-12-31]

For the metric visualization, choose Count as your metric aggregation, and filters as the bucket aggregation. You'll just need 1 filter bucket, and put in the query string query for that filter. Example:

image

Note that in my sample data, my start timestamp is @timestamp and my end timestamp is @timestamp_end, but use your own field names (obviously) :smiley:

For the time series question, when you make a date histogram in Elasticsearch, you have to choose a time field to use for the bucket keys. If you choose your start time, you can get a count of the licenses that have started per-time-bucket. If you choose the end time, you'll get a count of the licenses that end per-time-bucket. But it seems like there's not a way to get a count for the number of licenses that are active per-time-buckets, because you can't aggregate using a date math expression for the bucket keys.

Since this is the Kibana category and not the Elasticsearch category though, I'm not the best expert on querying Elasticsearch. You are more than welcome to ask this question in the Elasticsearch category.

1 Like
3

You may be able to do something like this.

4

Thanks a lot for the help, your suggestion drove me closer to my goal.
However I would like the date ranges not to be fix, but affected by time picker. I.e. starts should be
[* TO timepicker_max_time] AND ends should be [timepicker_min_time TO *] is that possible?

On another note, I can't seem to search for time ranges at all within the discover function, started a separate thread for that: Can't query for date or date range.

5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.