Date histogram facet


(Ricardo Monteiro) #1

Hi,

It's possible to create a date histogram facet using a string field to
retrieve the total of each hit spread by a day interval?

To ilustrate the problem.

I have this mapping:

{
"mappings": {
"log": {
"properties": {
"session": {"type": "string"},
"visitor": {"type": "string"},
"site": {"type": "integer"},
"access": {"type": "date", "format" : "yyyy-MM-dd
HH:mm:ss"},
"request": {"type": "string", "index" : "not_analyzed"},
"referer": {"type": "string", "index" : "not_analyzed"},
"first": {"type": "integer"},
"ip": {"type": "ip"},
"user_agent": {"type": "string"},
"city": {"type": "string", "index" : "not_analyzed"},
"country": {"type": "string", "index" : "not_analyzed"},
"ll": {"type": "geo_point"}
}
}
}
}

The request field is an url, like "/", "/about.html", "/contact.html", so
on...

I need to create a date histogram with a day interval an retrieve the sum
of each request.

The result that i want is something like:

2013-09-01 - 10 requests
2013-09-02 - 5 requests
2013-09-03 - 8 requests
2013-09-04 - 12 requests

I'm reading the elasticsearch docs, but i just find how to do if my field
is numeric using the key_field and value_field.

Well, thanks in advance for who try to help me.

Best regards
Ricardo Monteiro

SitePX - http://www.sitepx.com
Facebook - https://www.facebook.com/sitepx
Twitter - https://twitter.com/sitepx

--
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.
For more options, visit https://groups.google.com/groups/opt_out.


(Clinton Gormley) #2

I'm unclear as to whether you want a single request to give you a date
histogram for each page, or if a single request should target a single page
(eg about.html).

If the former, then that is not currently supported, as it requires a
hierarchical facet (think "pivot table"). The new aggregations framework
in 1.0 will support this.

For the single-request-for-single-page, you can use, eg:

POST /_search

{
"query": {
"filtered": {
"filter": {
"term": {
"url": "about.html"
}
}
}
},
"facets": {
"histo": {
"date_histogram": {
"field": "access",
"interval": "day"
}
}
}
}

On 6 September 2013 01:33, Ricardo Monteiro ricardo@sitepx.com wrote:

Hi,

It's possible to create a date histogram facet using a string field to
retrieve the total of each hit spread by a day interval?

To ilustrate the problem.

I have this mapping:

{
"mappings": {
"log": {
"properties": {
"session": {"type": "string"},
"visitor": {"type": "string"},
"site": {"type": "integer"},
"access": {"type": "date", "format" : "yyyy-MM-dd
HH:mm:ss"},
"request": {"type": "string", "index" : "not_analyzed"},
"referer": {"type": "string", "index" : "not_analyzed"},
"first": {"type": "integer"},
"ip": {"type": "ip"},
"user_agent": {"type": "string"},
"city": {"type": "string", "index" : "not_analyzed"},
"country": {"type": "string", "index" : "not_analyzed"},
"ll": {"type": "geo_point"}
}
}
}
}

The request field is an url, like "/", "/about.html", "/contact.html", so
on...

I need to create a date histogram with a day interval an retrieve the sum
of each request.

The result that i want is something like:

2013-09-01 - 10 requests
2013-09-02 - 5 requests
2013-09-03 - 8 requests
2013-09-04 - 12 requests

I'm reading the elasticsearch docs, but i just find how to do if my field
is numeric using the key_field and value_field.

Well, thanks in advance for who try to help me.

Best regards
Ricardo Monteiro

SitePX - http://www.sitepx.com
Facebook - https://www.facebook.com/sitepx
Twitter - https://twitter.com/sitepx

--
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.
For more options, visit https://groups.google.com/groups/opt_out.

--
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.
For more options, visit https://groups.google.com/groups/opt_out.


(rcmonteiro) #3

Thanks Clinton!

In fact i need the sum of requests, not the total of each request.

The idea is to plot a graph for analytics about the access of a single site, each day with the total requests.
In other words, a pageview graph.

Reading the ES docs i think date histogram facet was the solution.

Is there any workaround?

I have then documents saved as:

{
"session":"[session_id]",
"visitor":"[visitor_id]",
"site":"[site_id]",
"access":"[yyy-mm-dd hh:nn:ss]",
"request":"/about.html",
"referer":"[refererer]",
"first":"[0|1]",
"ip":"[ip]",
"user_agent":"[user_agent]",
"city":"[city]",
"country":"[country]",
"ll":{"lat": "[lat]", "lon": "[lon]"}
}

And i need to retrieve the sum of all requests group by day of access in a given date range.

My plan B is to retrieve all data in the given date range, put in array and work on it by coding some lines, this doesnt smells like a good plan.. lol

Thanks again!


(rcmonteiro) #4

Each log input is a request, that way i managed to retrieve what i want for requests.

{
"query" : {
"match_all" : {}
},
"facets" : {
"histo1" : {
"date_histogram" : {
"field" : "access",
"interval" : "day"
}
}
}
}

Now i need the same report, but grouping the request by session, to create a visits report.
And the only way that came up is to make one search for each day that a i need in my report, using the facets on the field "session" and the "access" on the terms filter.
But this is not cool, is the only way?


(system) #5