Get data for time range window

Hi,
lets have simple login data (@timestamp, id of user). This data we have for one month.

Can i create table / bar graph where will be count of logins (per day) for all month BUT only during 14:00 - 18:00.

something like :
12.1.2016 14:00 - 18:00 : 123 logins
12.2.2016 14:00 - 18:00 : 456 logins
12.3.2016 14:00 - 18:00 : 678 logins
12.4.2016 14:00 - 18:00 : 901 logins
...
It would be great if number of rows i would be able to control by change of date (right top corner, if i choose "Last 30 days" -> there will be 30 rows with count of logins from 14:00 to 18:00)

Which version of Elasticsearch are you running? Depending on that there's a couple different approaches we can take here.

ES 5 ... sorry i forgot

ES 5

Okay, awesome! That means you have access to Elasticsearch's new built-in scripting language, Painless, which will help us extract the time of day (hour, specifically) from the date.

There are two approaches available here. One involves parsing out the time of day (again, the hour, specifically) at indexing time and the other involves parsing it at query time. The first approach gives you more speed at search time but is less flexible (e.g. what if you want to look at the day of the week instead some time in the future?). The second approach is a bit slower at search time but gives you more flexibility. Depending on your needs, you can determine which approach might be a better fit. I will describe both below.

Approach 1: Parsing out the hour of day at indexing time.

Given that you are using the @timestamp field, I will assume you are using Logstash to ingest the data (but let me know if that's not the case). In your Logstash configuration, you can parse out the hour of the day from @timestamp by using the date filter plugin like so:

filter {
  mutate {
    add_field => { hour_of_day => "%{+HH}" }
  }
}

Now all your documents in Elasticsearch will have an additional field, hour_of_day which will contain the 24-hour-formatted hour of day (0..23).

You can then create the bar chart visualization in Kibana like so:

  1. Use a date histogram aggregation for the x-axis buckets. Set the field to @timestamp and interval to daily.
  2. In the search bar towards the top of the page (whose default value is set to *), filter by the specific hour range you want, e.g. hour_of_day:[14 TO 18]. You can learn more about this syntax here.

Approach 2: Parsing out the hour of day at search time

Unfortunately this approach is not very straightforward at the moment :frowning: so please bear with the unintuitive steps:

In your browser, load up Kibana and go to the Discover page. Make sure the index that has the login data is selected.

The left pane on this page shows a list of fields. Click on any field (doesn't matter which one). It will expand the field below showing the top (most frequently occurring) values for the field and their frequencies as percentages. There will also be small - and + icons next to each row. Click on the + icon for one of the rows (again, doesn't matter which one). This will create a little filter on the top of the page, right under the search bar.

Click the pencil icon on the filter to edit it. This will show you a form with two fields: a text box for the "Filter Alias" and a text area with the filter definition in JSON in it. Set the "Filter Alias" field to something descriptive like "hour of day". Replace the filter definition JSON with the following:

 { 
   "script": {
     "script": {
       "inline": "def hour = Instant.ofEpochMilli(doc['@timestamp'].value).atZone(ZoneId.of('Z')).toLocalTime().getHour(); return hour >= params.min && hour < params.max;",
       "lang": "painless",
       "params": {
         "min": 4,
         "max": 7
       }
     }
   }
 }
Here you can adjust the `min` and `max` to the hours you want.

Save the search - the Save button is in the top nav.

Go the Visualize page and choose the Vertical Bar Chart visualization. Choose the search you just saved in the previous step (from the right half of the page). This will drop you into the visualization editor page.

Select the X-axis bucket type, Date Histogram aggregation, @timestamp field, and Daily interval. Click the "Apply Changes" play button on the top of the visualization editor form pane.

This should show you the bar chart you want.

3 Likes

Thank you very much ! it works perfect

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.