How to visualize Event Distribution by Daytime?

[Kevin_Wiegand]

I hope I'm able to describe my problem...
i'm monitoring a system where students can login to register for exams. Now it would be interesting to do a visualisation which show at which daytime how many students try to login to the system. This would help to figure out the relation between logins and other occuring errors.

So i need a Timeline with a 24h X-Axis and showing the amount of logins on the y-axis. I dont care about on which day the login were made..only the distribution on the daytime.

Thanks for your help!

[warkolm (Mark Walkom)]

Have a look at https://www.elastic.co/guide/en/elasticsearch/reference/5.5/_use_of_a_script_to_aggregate_by_day_of_the_week.html

[LeeDr (Lee Drengenberg)]

Since this post is in Kibana channel, I'll show Mark's solution in Kibana.

First create a scripted field in Kibana on your index pattern something like this;

LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['@timestamp'].value.millis), ZoneId.of('US/Central')).getHour()
where @timestamp is the date type field my index is based on and US/Central is my time zone.

You could use a simpler scripted field like doc["@timestamp"].date.hourOfDay but it would show the hours in UTC time.

My data shown here in Discover is daily periodic;

Now I can create a heatmap visualization over a period of a week, or as long as I want and see the hourly trend per day;

[Kevin_Wiegand]

Thank you very much for your help but here are my experience with your solution:

When i try to use:

LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['@timestamp'].value.millis), ZoneId.of('US/Central')).getHour()

Everytime i click on Discovery Tab Kibana showed a warning like this and no search results:

Courier fetch: 5 of 50 Shards failed

Also elasticsearch.log tells me:

Caused by: java.lang.IllegalArgumentException: Unable to find dynamic field [millis] for class [java.lang.Long]

After some Research i found many issues with the Threadpoolsize in relation to the Courier fetch..-Error, so i tried to set:

thread_pool.search.queue_size: 100000

As a result my instance of ElasticSearch was broken and i can't get it to restart. So I deleted the new setting in elasticsearch.yml and modified the scripted field and used your second suggestion. This worked without any Errors but as you said it shows the hour in UTC.

Finally i found another suggestions which seems to work and differes just a bit from yours:

LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['@timestamp'].value), ZoneId.of('Asia/Shanghai')).getHour()

[LeeDr (Lee Drengenberg)]

Sorry about that. The .millis is needed to fix it on the upcoming 6.0 release. But if you're on a 5.x release you should leave it off.

[warkolm (Mark Walkom)]

As usual, @LeeDr is amazing