Aggregate count based on Time

[kb2295]

I have an index in which each line has the field "name" and there are only about 15 names I'm dealing with. I also have a field called "lastUpdated" which is a of type Date.

What I have is a pie chart of names by count. For example: "Alex" appears 200 times, "Charlie" appears 100 times etc.

The problem is, I don't want names counted more than once if they appear in the same second of the "lastUpdated" timestamp.

What I want is to get the count by timestamp. For example, if "Bob" appears 156 times in between the "lastUpdated" stamp of 12:00:00 and 12:00:01, I want "Bob" to be counted only once. So, if "lastUpdated" timestamp is between 12:00:00 and 12:00:01, and we have "Bob" 156 times, "Charlie" 100 times and "Alice" 20 times, each name will get counted only once (Alice =1, Bob =1, Charlie =1). Now, between 12:00:01 and 12:00:02 if Bob appears 2 times, Alice appears 50 times, the aggregated values should be (Alice =2, Bob =2, charlie=1).

I simply want all of this to appear in a pie chart.

Can anyone help me out with this?

Thank you.

[Brandon_Kobel]

Hey @kb2295, you can use the Unique Count aggregation similar to the following to get close to what you're looking for:

However, the count are approximate and the following does a good job of explaining the details between the counts: https://www.elastic.co/guide/en/elasticsearch/reference/6.5/search-aggregations-metrics-cardinality-aggregation.html#_counts_are_approximate

[kb2295]

Yes, but my timestamps also include miliseconds. Is there a way to round to the nearest second and then aggregate it?

[Brandon_Kobel]

@kb2295 you can use a Scripted Field to truncate the milliseconds and then do a Unique Count. The following scripted field will truncate the millisecond: doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)

[kb2295]

The following error occurs when I use that script:

Error: Request to Elasticsearch failed: {"error":{"root_cause":[{"type":"script_exception","reason":"runtime error","script_stack":["doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)"," ^---- HERE"],"script":"doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)","lang":"painless"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"fetch","grouped":true,"failed_shards":[{"shard":0,"index":"fusion_clearingtrade","node":"GcYQdh54S3a5Zvq8hkyEcg","reason":{"type":"script_exception","reason":"runtime error","script_stack":["doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)"," ^---- HERE"],"script":"doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)","lang":"painless","caused_by":{"type":"illegal_argument_exception","reason":"Unable to find dynamic method [getMillisOfSecond] with [0] arguments for class [java.lang.Long]."}}}]},"status":500}

[kb2295]

[Brandon_Kobel]

@kb2295

Unable to find dynamic method [getMillisOfSecond] with [0] arguments for class [java.lang.Long]."

Is your @timestamp field a Date type? What version of ES/Kibana are you running?

[kb2295]

Yes, it's a date. I tried with another Date field too, didn't work.

Version: 5.6.12