Date Aggregation Help


(Simon Edwards) #1

Hi,

I was wondering if somebody familiar with aggregations, particularly date
histogram aggregations, can point me in the right direction.

I'm currently looking to get a total count of records over a specific time
period. Each record contains a "date_submitted" field and if they're
closed, contain a "date_closed" field.

Is it possible to aggregate the records based off these values (i.e. only
showing open issues for a weekly period, even if the record was submitted a
year ago)? If so where abouts are the aggregations specified? In the
dashboard JSON or within the index mapping?

Many thanks in advance.

--
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/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(vineeth mohan-2) #2

Hello Simon ,

I believe this can be done in this manner.
Do 2 separate date histogram on the date_submitted field and date_closed
field.
The sum of count of date_submitted minus the sum of count on date_closed
on all the previous date should give you the number of open issues for that
week.

For eg:

Week1 - Open - 10 , closed - 5
Week2 - Open 20 m ,closed 6
Week3 - Open 30 , closed 10

Number of open issues on
Week1 - 10
Week2 - (20 + 10 ) - 5 = 25
Week3 - ( 30 + 20 + 10 ) - (6 + 5) = 49

Thanks
Vineeth

On Mon, Sep 1, 2014 at 5:25 PM, Simon Edwards simon_edwards@scee.net
wrote:

Hi,

I was wondering if somebody familiar with aggregations, particularly date
histogram aggregations, can point me in the right direction.

I'm currently looking to get a total count of records over a specific time
period. Each record contains a "date_submitted" field and if they're
closed, contain a "date_closed" field.

Is it possible to aggregate the records based off these values (i.e. only
showing open issues for a weekly period, even if the record was submitted a
year ago)? If so where abouts are the aggregations specified? In the
dashboard JSON or within the index mapping?

Many thanks in advance.

--
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/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
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/CAGdPd5m3oacS75hV6nSeXR0ZasC7RyL2SqDh1vk%2B1VXUKFNd_w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.


(Simon Edwards) #3

Hello Vineeth,

Many thanks for your reply, sounds like your method should work a treat!

One quick noob question, where abouts are the date histograms aggregations
created? Are they created on the index or on the dashboard itself? Any help
is appreciated.

Cheers,
Si.

On Monday, September 1, 2014 2:11:02 PM UTC+1, vineeth mohan wrote:

Hello Simon ,

I believe this can be done in this manner.
Do 2 separate date histogram on the date_submitted field and date_closed
field.
The sum of count of date_submitted minus the sum of count on date_closed
on all the previous date should give you the number of open issues for that
week.

For eg:

Week1 - Open - 10 , closed - 5
Week2 - Open 20 m ,closed 6
Week3 - Open 30 , closed 10

Number of open issues on
Week1 - 10
Week2 - (20 + 10 ) - 5 = 25
Week3 - ( 30 + 20 + 10 ) - (6 + 5) = 49

Thanks
Vineeth

On Mon, Sep 1, 2014 at 5:25 PM, Simon Edwards <simon_...@scee.net
<javascript:>> wrote:

Hi,

I was wondering if somebody familiar with aggregations, particularly date
histogram aggregations, can point me in the right direction.

I'm currently looking to get a total count of records over a specific
time period. Each record contains a "date_submitted" field and if they're
closed, contain a "date_closed" field.

Is it possible to aggregate the records based off these values (i.e. only
showing open issues for a weekly period, even if the record was submitted a
year ago)? If so where abouts are the aggregations specified? In the
dashboard JSON or within the index mapping?

Many thanks in advance.

--
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 elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
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/b9df0286-fac1-41be-89a5-be062e8a9f55%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(vineeth mohan-2) #4

Hello Simon ,

I didn't quite understand your question.
Kindly elaborate.

Thanks
Vineeth

On Mon, Sep 1, 2014 at 7:59 PM, Simon Edwards simon_edwards@scee.net
wrote:

Hello Vineeth,

Many thanks for your reply, sounds like your method should work a treat!

One quick noob question, where abouts are the date histograms aggregations
created? Are they created on the index or on the dashboard itself? Any help
is appreciated.

Cheers,
Si.

On Monday, September 1, 2014 2:11:02 PM UTC+1, vineeth mohan wrote:

Hello Simon ,

I believe this can be done in this manner.
Do 2 separate date histogram on the date_submitted field and date_closed
field.
The sum of count of date_submitted minus the sum of count on date_closed
on all the previous date should give you the number of open issues for that
week.

For eg:

Week1 - Open - 10 , closed - 5
Week2 - Open 20 m ,closed 6
Week3 - Open 30 , closed 10

Number of open issues on
Week1 - 10
Week2 - (20 + 10 ) - 5 = 25
Week3 - ( 30 + 20 + 10 ) - (6 + 5) = 49

Thanks
Vineeth

On Mon, Sep 1, 2014 at 5:25 PM, Simon Edwards simon_...@scee.net wrote:

Hi,

I was wondering if somebody familiar with aggregations, particularly
date histogram aggregations, can point me in the right direction.

I'm currently looking to get a total count of records over a specific
time period. Each record contains a "date_submitted" field and if they're
closed, contain a "date_closed" field.

Is it possible to aggregate the records based off these values (i.e.
only showing open issues for a weekly period, even if the record was
submitted a year ago)? If so where abouts are the aggregations specified?
In the dashboard JSON or within the index mapping?

Many thanks in advance.

--
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 elasticsearc...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%
40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
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/b9df0286-fac1-41be-89a5-be062e8a9f55%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/b9df0286-fac1-41be-89a5-be062e8a9f55%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
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/CAGdPd5no_tFxcNbh9huv1Bos2hjE-B-mWq5%3DL83vKg-ygROT3Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.


(Simon Edwards) #5

Hello Vineeth,

My question was regarding where to set up the date histograms. Do i simply
add a section to the dashboard JSON or do I need to update the
index field mappings? If you could provide a small example that'd be great.

Thanks for all your help!

On Monday, September 1, 2014 4:12:14 PM UTC+1, vineeth mohan wrote:

Hello Simon ,

I didn't quite understand your question.
Kindly elaborate.

Thanks
Vineeth

On Mon, Sep 1, 2014 at 7:59 PM, Simon Edwards <simon_...@scee.net
<javascript:>> wrote:

Hello Vineeth,

Many thanks for your reply, sounds like your method should work a treat!

One quick noob question, where abouts are the date histograms
aggregations created? Are they created on the index or on the dashboard
itself? Any help is appreciated.

Cheers,
Si.

On Monday, September 1, 2014 2:11:02 PM UTC+1, vineeth mohan wrote:

Hello Simon ,

I believe this can be done in this manner.
Do 2 separate date histogram on the date_submitted field and date_closed
field.
The sum of count of date_submitted minus the sum of count on
date_closed on all the previous date should give you the number of open
issues for that week.

For eg:

Week1 - Open - 10 , closed - 5
Week2 - Open 20 m ,closed 6
Week3 - Open 30 , closed 10

Number of open issues on
Week1 - 10
Week2 - (20 + 10 ) - 5 = 25
Week3 - ( 30 + 20 + 10 ) - (6 + 5) = 49

Thanks
Vineeth

On Mon, Sep 1, 2014 at 5:25 PM, Simon Edwards simon_...@scee.net
wrote:

Hi,

I was wondering if somebody familiar with aggregations, particularly
date histogram aggregations, can point me in the right direction.

I'm currently looking to get a total count of records over a specific
time period. Each record contains a "date_submitted" field and if they're
closed, contain a "date_closed" field.

Is it possible to aggregate the records based off these values (i.e.
only showing open issues for a weekly period, even if the record was
submitted a year ago)? If so where abouts are the aggregations specified?
In the dashboard JSON or within the index mapping?

Many thanks in advance.

--
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 elasticsearc...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%
40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
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 elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/b9df0286-fac1-41be-89a5-be062e8a9f55%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/b9df0286-fac1-41be-89a5-be062e8a9f55%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
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/aadc5927-f268-4016-a8b9-da3d6512e0b4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(vineeth mohan-2) #6

Hello Simon ,

I hope you are familiar with Elasticsearch API.
I believe by dashboard you are meaning Kibana.
If that is the case , you wont be able to see this result there as Kibana
does not currently offers this level of flexibility.

You will need to write JSON agg query based on -
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html#search-aggregations-bucket-datehistogram-aggregation

There need not be any work on schma or index to use this.
Hope that helps.

Thanks
Vineeth

On Mon, Sep 1, 2014 at 8:59 PM, Simon Edwards simon_edwards@scee.net
wrote:

Hello Vineeth,

My question was regarding where to set up the date histograms. Do i simply
add a section to the dashboard JSON or do I need to update the
index field mappings? If you could provide a small example that'd be great.

Thanks for all your help!

On Monday, September 1, 2014 4:12:14 PM UTC+1, vineeth mohan wrote:

Hello Simon ,

I didn't quite understand your question.
Kindly elaborate.

Thanks
Vineeth

On Mon, Sep 1, 2014 at 7:59 PM, Simon Edwards simon_...@scee.net wrote:

Hello Vineeth,

Many thanks for your reply, sounds like your method should work a treat!

One quick noob question, where abouts are the date histograms
aggregations created? Are they created on the index or on the dashboard
itself? Any help is appreciated.

Cheers,
Si.

On Monday, September 1, 2014 2:11:02 PM UTC+1, vineeth mohan wrote:

Hello Simon ,

I believe this can be done in this manner.
Do 2 separate date histogram on the date_submitted field and
date_closed field.
The sum of count of date_submitted minus the sum of count on
date_closed on all the previous date should give you the number of open
issues for that week.

For eg:

Week1 - Open - 10 , closed - 5
Week2 - Open 20 m ,closed 6
Week3 - Open 30 , closed 10

Number of open issues on
Week1 - 10
Week2 - (20 + 10 ) - 5 = 25
Week3 - ( 30 + 20 + 10 ) - (6 + 5) = 49

Thanks
Vineeth

On Mon, Sep 1, 2014 at 5:25 PM, Simon Edwards simon_...@scee.net
wrote:

Hi,

I was wondering if somebody familiar with aggregations, particularly
date histogram aggregations, can point me in the right direction.

I'm currently looking to get a total count of records over a specific
time period. Each record contains a "date_submitted" field and if they're
closed, contain a "date_closed" field.

Is it possible to aggregate the records based off these values (i.e.
only showing open issues for a weekly period, even if the record was
submitted a year ago)? If so where abouts are the aggregations specified?
In the dashboard JSON or within the index mapping?

Many thanks in advance.

--
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 elasticsearc...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40goo
glegroups.com
https://groups.google.com/d/msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
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 elasticsearc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/b9df0286-fac1-41be-89a5-be062e8a9f55%
40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/b9df0286-fac1-41be-89a5-be062e8a9f55%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
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/aadc5927-f268-4016-a8b9-da3d6512e0b4%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/aadc5927-f268-4016-a8b9-da3d6512e0b4%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
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/CAGdPd5m8g7frbMpfviXu7Enh7ncJmfsCingUKuwGS%2BPEOntbLg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.


(Simon Edwards) #7

Thanks for all your help Vineeth

On Monday, September 1, 2014 5:03:38 PM UTC+1, vineeth mohan wrote:

Hello Simon ,

I hope you are familiar with Elasticsearch API.
I believe by dashboard you are meaning Kibana.
If that is the case , you wont be able to see this result there as Kibana
does not currently offers this level of flexibility.

You will need to write JSON agg query based on -
http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html#search-aggregations-bucket-datehistogram-aggregation

There need not be any work on schma or index to use this.
Hope that helps.

Thanks
Vineeth

On Mon, Sep 1, 2014 at 8:59 PM, Simon Edwards <simon_...@scee.net
<javascript:>> wrote:

Hello Vineeth,

My question was regarding where to set up the date histograms. Do i
simply add a section to the dashboard JSON or do I need to
update the index field mappings? If you could provide a small example
that'd be great.

Thanks for all your help!

On Monday, September 1, 2014 4:12:14 PM UTC+1, vineeth mohan wrote:

Hello Simon ,

I didn't quite understand your question.
Kindly elaborate.

Thanks
Vineeth

On Mon, Sep 1, 2014 at 7:59 PM, Simon Edwards simon_...@scee.net
wrote:

Hello Vineeth,

Many thanks for your reply, sounds like your method should work a treat!

One quick noob question, where abouts are the date histograms
aggregations created? Are they created on the index or on the dashboard
itself? Any help is appreciated.

Cheers,
Si.

On Monday, September 1, 2014 2:11:02 PM UTC+1, vineeth mohan wrote:

Hello Simon ,

I believe this can be done in this manner.
Do 2 separate date histogram on the date_submitted field and
date_closed field.
The sum of count of date_submitted minus the sum of count on
date_closed on all the previous date should give you the number of open
issues for that week.

For eg:

Week1 - Open - 10 , closed - 5
Week2 - Open 20 m ,closed 6
Week3 - Open 30 , closed 10

Number of open issues on
Week1 - 10
Week2 - (20 + 10 ) - 5 = 25
Week3 - ( 30 + 20 + 10 ) - (6 + 5) = 49

Thanks
Vineeth

On Mon, Sep 1, 2014 at 5:25 PM, Simon Edwards simon_...@scee.net
wrote:

Hi,

I was wondering if somebody familiar with aggregations, particularly
date histogram aggregations, can point me in the right direction.

I'm currently looking to get a total count of records over a specific
time period. Each record contains a "date_submitted" field and if they're
closed, contain a "date_closed" field.

Is it possible to aggregate the records based off these values (i.e.
only showing open issues for a weekly period, even if the record was
submitted a year ago)? If so where abouts are the aggregations specified?
In the dashboard JSON or within the index mapping?

Many thanks in advance.

--
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 elasticsearc...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40goo
glegroups.com
https://groups.google.com/d/msgid/elasticsearch/5e448bc2-007b-4c7b-a073-fcb1a8017eed%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit https://groups.google.com/d/optout.

--
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 elasticsearc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/
msgid/elasticsearch/b9df0286-fac1-41be-89a5-be062e8a9f55%
40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/b9df0286-fac1-41be-89a5-be062e8a9f55%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
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 elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/aadc5927-f268-4016-a8b9-da3d6512e0b4%40googlegroups.com
https://groups.google.com/d/msgid/elasticsearch/aadc5927-f268-4016-a8b9-da3d6512e0b4%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit https://groups.google.com/d/optout.

--
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/6c42d7aa-fd64-46f8-abd1-c15ef9d2cf60%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


(system) #8