Hello there,
I know how to use an aggregation to query ES to display only unique values for a field, but I can't find how to do this in a Kibana "Bar Vertical" visualization:
I have found how to do it for a "Pie" visualization:
I'd like to do the same for "Bar Vertical" and "filter" on unique_count(distribution.instanceId).
If it helps, here's the dashboard: Elastic
Any idea?
Thanks a lot
Hi @vmassol
I am not sure exactly what you are trying to do, but I think you just want to do a vertical bar graph. (not stacked, not percentage)
With Horizontal Axis: Top Values java.version
With Vertical axis: unique_count(distribution.instanceId)
Here is mine where
Horizontal Axis : Top Values of service.name ranked by descending unique_count(kubernetes.pod.name)
Vertical Axis : unique_count(kubernetes.pod.name) is the vertical axis
I adjusted the Axis to show all values
Thanks for your answer. This is not what I'm looking for.
I want the following (see my screenshot in the first post for the visual of it):
date.currentusers.totalBUT considering only entries that are unique for the distribution.instanceId field (i.e. unique_count(distribution.instanceId)).
Thanks
Actually, to be even more precise, I'd like to find a way to have a date histogram visualization in Kibana, for "sumusers" below (defined using an ES query):
"aggs": {
"by_day": {
"date_histogram": {
"field": "date.current",
"calendar_interval": "1d",
"time_zone": "Europe/Paris"
},
"aggs": {
"instanceId_count": {
"cardinality": {
"field": "distribution.instanceId"
}
},
"max": {
"terms": {
"field": "distribution.instanceId",
"size": "2000"
},
"aggs":{
"MAX_USERS":{
"max": {
"field": "users.total"
}
},
}
},
"sumusers":{
"sum_bucket": {
"buckets_path": "max>MAX_USERS"
}
}
}
}
As you can see, the data is first aggregated on unique distribution.instanceId, then we find the max(users.total)and then we sum all max users.
The idea is to have a graph:
users.total per unique distribution.instanceIdIs that possible?
Thanks
Since I can't get to your data
What you are describing is a Series Aggregation by Sum of the Series.
The series being the max users.total by distribution.instanceId
In Lens Bar Chart
users.totaldistribution.instanceId and collapse by SumThis Sum, sums all the max users.total per distribution.instanceId into a total sum.
The Collapse By is the Key
Thanks a lot @stephenb!
I just can't find how to specify the unique distribution.instanceId in the breakdown config:
Thanks again, that's super helpful.
Note: You should be able to access Elastic normally.
Apologies ... Top Values is Terms so it will be each distinct value of distribution.instanceId
But in short, if you want to break down by cardinality (which I am not even sure what that means) ... because in a time bucket, there will only be 1 cardinality value on a given field.
can you share the result of the agg query above... I am not sure you will be able to do this in lens.
In the query above there is no time dimension... so I am unclear on that...
so is the X Axis a time bucket or a cardinality bucket? because within 1 timebucket there will only be 1 value for cardnality
Yes I looked it is read-only (I understand why) so that is what I meant (I don't expect you to make it writeable)
cc @Marco_Liberati Any thoughts on this?
Thanks again @stephenb
So I've used what was in my screenshot above (ie "top values") and when I inspect the graph, I see that the following request is made (I'm just showing the aggregations part):
"aggs": {
"0": {
"terms": {
"field": "distribution.instanceId",
"order": {
"0-orderAgg": "desc"
},
"size": 5,
"shard_size": 25
},
"aggs": {
"1": {
"date_histogram": {
"field": "date.current",
"calendar_interval": "1w",
"time_zone": "Europe/Paris",
"extended_bounds": {
"min": 1654095295706,
"max": 1730908914940
}
},
"aggs": {
"2": {
"max": {
"field": "users.total"
}
}
}
},
"0-orderAgg": {
"cardinality": {
"field": "distribution.instanceId"
}
}
}
}
That looks quite different from the agg I had posted above.
Full query:
URL: https://...domain.../activeinstalls2/installs3/_search?pretty Query:
{
"query": {
"bool": {
"must" : {
"query_string": {
"query": "distribution.extension.version:* AND -distribution.extension.version:*rc* AND -distribution.extension.version:*milestone* AND -distribution.instanceId:xxx"
}
},
"must_not": [
{
"bool": {
"filter": [
{
"wildcard": {
"distribution.extension.version": "*SNAPSHOT"
}
},
{
"wildcard": {
"distribution.extension.id": "*staging"
}
},
{
"wildcard": {
"distribution.extension.id": "*rc*"
}
}
]
}
}
],
"filter": [
{
"script" : {
"script" : "((doc['date.current'].value.toEpochSecond() - doc['date.first'].value.toEpochSecond()) >= 86400*5)"
}
},
{ "range": {
"date.current": {
"gte": "now-30d"
}
}
}]
}
},
"size": 0,
"fields": [
{
"field": "date.current",
"format": "date_time"
}],
"aggs": {
"by_day": {
"date_histogram": {
"field": "date.current",
"calendar_interval": "week",
"time_zone": "Europe/Paris"
},
"aggs": {
"instanceId_count": {
"cardinality": {
"field": "distribution.instanceId"
}
},
"max": {
"terms": {
"field": "distribution.instanceId",
"size": 2000
},
"aggs":{
"MAX_USERS":{
"max": {
"field": "users.total"
}
},
"MAX_DOCS":{
"max": {
"field": "documents.total"
}
},
"MAX_WIKIS":{
"max": {
"field": "wikis.total"
}
}
}
},
"sumusers":{
"sum_bucket": {
"buckets_path": "max>MAX_USERS"
}
},
"sumdocs":{
"sum_bucket": {
"buckets_path": "max>MAX_DOCS"
}
},
"sumwikis":{
"sum_bucket": {
"buckets_path": "max>MAX_WIKIS"
}
}
}
}
}
}
Results:
{
"took" : 315,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"by_day" : {
"buckets" : [
{
"key_as_string" : "2024-10-07T00:00:00.000+02:00",
"key" : 1728252000000,
"doc_count" : 22259,
"max" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 9843,
"buckets" : [
{
"key" : "...key1...",
"doc_count" : 639,
"MAX_USERS" : {
"value" : 1.0
},
"MAX_DOCS" : {
"value" : 1391.0
},
"MAX_WIKIS" : {
"value" : 1.0
}
},
{
"key" : "...key2...",
"doc_count" : 468,
"MAX_USERS" : {
"value" : 45.0
},
"MAX_DOCS" : {
"value" : 1015.0
},
"MAX_WIKIS" : {
"value" : 1.0
}
},
{
"key" : "...key3...",
"doc_count" : 109,
"MAX_USERS" : {
"value" : 145.0
},
"MAX_DOCS" : {
"value" : 3211.0
},
"MAX_WIKIS" : {
"value" : 1.0
}
},
...
FTR we've coded a visualization in XWiki, based on the agg above and the 2 graphs don't look similar for the same period:
In XWiki, based on the query just above:
In Kibana, based on the Lens you helped me with:
If I can't do it in Lens, do I need to use a different visualization? Like using Vega (which I'd need to learn ;))?
Thanks again
© 2020. All Rights Reserved - Elasticsearch
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant logo are trademarks of the Apache Software Foundation in the United States and/or other countries.
