Mixing Range aggregation and computing days between 2 dates?

Elastic Stack Elasticsearch
1

Hi ES experts,

I have instances (XWiki instances to be precise) sending pings every day to
an ES server (the model is very simple: one _timestamp field for the date
and an "instanceId" field for uniquely identifying the instance sending the
ping).

I'd like to find how to write a query that shows the average number of days
each instance has been sending pings, by displaying counts for instances
having their last ping date - first ping date < 1 day, between 1 day and 7
days, between 7 days and 365 days and over 365 days.

I've tried several aggregations but I can't find one that will work out.

I'd like something like this:

curl -XGET
"http://localhost:9200/installs/install/_search?search_type=count&pretty=1"
-d'
{
"aggs": {
"installs_by_range" : {
"range" : {
"script" : "<compute max_date - min_date in days here>",
"ranges" : [
{ "to" : 2 },
{ "from" : 2, "to" : 30 },
{ "from" : 30, "to" : 365 },
{ "from" : 365 }
]
},
"aggs" : {
"instanceIds" : {
"terms" : { "field" : "instanceId" },
"aggs" : {
"min_date" : { "min" : { "field" : "_timestamp" } },
"max_date" : { "max" : { "field" : "_timestamp" } }
}
}
}
}
}
}'

Here's a script to generate some minimal data:
http://design.xwiki.org/xwiki/bin/view/Proposal/ActiveInstalls2#HData

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/0541a994-e254-4056-b294-20a036b0616b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

2

On Friday, April 4, 2014 3:45:15 PM UTC+2, Vincent Massol wrote:

Hi ES experts,

I have instances (XWiki instances to be precise) sending pings every day
to an ES server (the model is very simple: one _timestamp field for the
date and an "instanceId" field for uniquely identifying the instance
sending the ping).

I'd like to find how to write a query that shows the average number of
days each instance has been sending pings, by displaying counts for
instances having their last ping date - first ping date < 1 day, between 1
day and 7 days, between 7 days and 365 days and over 365 days.

I've tried several aggregations but I can't find one that will work out.

I'd like something like this:

curl -XGET "
http://localhost:9200/installs/install/_search?search_type=count&pretty=1"
-d'
{
"aggs": {
"installs_by_range" : {
"range" : {
"script" : "<compute max_date - min_date in days here>",
"ranges" : [
{ "to" : 2 },
{ "from" : 2, "to" : 30 },
{ "from" : 30, "to" : 365 },
{ "from" : 365 }
]
},
"aggs" : {
"instanceIds" : {
"terms" : { "field" : "instanceId" },
"aggs" : {
"min_date" : { "min" : { "field" : "_timestamp" }
},
"max_date" : { "max" : { "field" : "_timestamp" } }
}
}
}
}
}
}'

Here's a script to generate some minimal data:
http://design.xwiki.org/xwiki/bin/view/Proposal/ActiveInstalls2#HData

oops forget to finish my email :wink:

---- end of previous email ----

Thanks a lot for any pointer!
-Vincent

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/e809837a-0e14-400b-8ed8-a652616dcab8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.