Kibana Filter for a specific time range

Elastic Stack Kibana
1

Hi,

I am new to Elastic that are trying it out, i am looking forward to generate report to display windows users logon after office hour via elastic cloud with only 1 winlogbeat in VM.

However i am currently stuck with filtering the specific time from 6 PM to 9 AM as I cant figure out the syntax to create the query for filtering the working hour out.

I tried to query a match for the time stamp for an exact time as shown below, it works but I have no idea how to specify a range for it.

Could anyone advice me on how to construct the filter to filter a range of Time only?
like using wildcard (Example: "@timestamp": "*T18:00:00Z" ) (which does not work..)

{
  "query": {
    "match": {
      "@timestamp": "2017-10-24T07:01:24.075Z"
    }
  }
}

Below image is the filter, table and json of a sample event log

image
image.png1570×797 65.1 KB

JSON

  {
      "_index": "winlogbeat-2017.10.24",
      "_type": "doc",
      "_id": "AV9NL5m4wjSxakGF8vCE",
      "_version": 1,
      "_score": null,
      "_source": {
        "@timestamp": "2017-10-24T07:01:24.075Z",
        "beat": {
          "hostname": "Win2008Elastic",
          "name": "Win2008Elastic",
          "version": "5.5.2"
        },

Also why the time in @timestamp for Table and JSON is different..?

1 Like
2

Hi,

You can actually not really filter on a date field like you wish.

If you want to filter for the specific hour of the day, you would need to extract that into it's own field.
You can either do this via a scripted field, in the index pattern settings or (which would be more performant) before indexing the documents (aka in any kind of pre-processing step), but that would mean, you need to add some pre-processing before inserting the data from beats into Elasticsearch.

If you add a scripted field, you can use the following painless script (and set the field type to number):

doc['@timestamp'].date.hourOfDay + 2

Since Elasticsearch stores dates internally as pure UTC timestamps, you need to manually add/subtract your timezone to it, since ES doesn't have a knowledge about that anymore. In that case we are assuming UTC+2.

You can now easily create a filter in Discover for that field, and say, it must be within two values, or have a specific value, etc:

screenshot-20171025-151421

Cheers,
Tim

3 Likes
3

Hi Timroes,

Wow, Thanks for the replying! It works just the way I wish to achieve!

Thanks Tim!!!

4

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