topic Re: How to create a chart to show count of events by hour over days in a week? in Dashboards & Visualizations

How to create a chart to show count of events by hour over days in a week?

CWH617 — Thu, 28 Jun 2018 02:36:02 GMT

Below is the search query i used in order to get a similar chart but the hours are not consecutive, as shown in the Legend's table on the right side. What i have in mind was to create a chart that displays the count of high severity events by hour in a day for a week and have the chart start on a Monday and ends on a Sunday instead of starting on the current day

I have went and search for various and multiple sources on how to solve this problem and tried using %w, earliest=+7d@w1, | bin span=1d, and so on in my queries in trying to create the desired chart

Thanks and looking forward to replies

Re: How to create a chart to show count of events by hour over days in a week?

DalJeanis — Thu, 28 Jun 2018 02:48:32 GMT

First, you want the count by hour, so you need to bin by hour. Second, once you've added up the bins, you need to present teh output in terms of day and hour.

Here's one version. You can swap the order of hour and day in the chart command if you prefer to swap the column and row headers.

your search that gets the events you want
| bin _time as hour span=1h
| stats count as hourcount by hour
| bin hour as day span=1d
| eval day=strftime(day,"%Y-%m-%d")
| eval hour=strftime(hour,"%H:%M")
| chart sum(hourcount) as count by hour day

Re: How to create a chart to show count of events by hour over days in a week?

CWH617 — Thu, 28 Jun 2018 03:26:31 GMT

Hi thanks for your fast reply. I have tried the search query you provided but the problem still lies on the chart not being able to produce events for every single hour from 00:00 to 23:00 and also not in a full week i.e from Monday to Sunday.

The last picture are a separate "hours in a day" and "days in a week" chart. Initially this was the desired output i wanted to have, but the resulting search query im trying to do is a combination of both these charts into one where one day has all individual hours shown in the chart together with the rest of the days and display altogether as a single graph

Re: How to create a chart to show count of events by hour over days in a week?

CWH617 — Thu, 28 Jun 2018 03:28:06 GMT

Hi DalJeanis, i was unable to attach photos in the comments sections so i have posted my reply as an answer. Please do take a look at it. Thanks

Re: How to create a chart to show count of events by hour over days in a week?

Sukisen1981 — Thu, 28 Jun 2018 10:25:20 GMT

Hi, Have you tried swapping hour day as mentioned by @DalJeanis?
you have | chart sum(hourcount) as count by day hour
instead of
| chart sum(hourcount) as count by hour day

Re: How to create a chart to show count of events by hour over days in a week?

CWH617 — Thu, 28 Jun 2018 11:25:03 GMT

Hi @Sukisen1981. Yes i did. count by hour day was the initial query @DalJeanis provided

Re: How to create a chart to show count of events by hour over days in a week?

Sukisen1981 — Thu, 28 Jun 2018 11:38:20 GMT

strange indeed, either we are not understanding your use case or there is something weird going on. Try this query , run it as it is since the audit index is a default one
index="_audit" | bin _time as hour span=1h
| stats count as hourcount by hour
| bin hour as day span=1d
| eval day=strftime(day,"%Y-%m-%d")
| eval hour=strftime(hour,"%H:%M")
| chart sum(hourcount) as count by hour,day

Now, I ran this over a week, month to date and I do receive the hours on xaxis, days on the yaxis...Are you not receiving the same output?

Re: How to create a chart to show count of events by hour over days in a week?

CWH617 — Thu, 28 Jun 2018 12:20:16 GMT

@Sukisen1981, i got similar results where days are on the y-axis and hours on the x-axis, but what i was trying to do is to sort of have like a dual x-axis, where the events would show at a one hour interval, and it would show at a span of 1 week

Re: How to create a chart to show count of events by hour over days in a week?

CWH617 — Fri, 29 Jun 2018 02:40:58 GMT

Hi @Sukisen, i have gotten the same output but its not what i was looking for. Below shows the chart i wanted to achieve, with the time from 00:00 to 23:00 in one day concurrently, and then to display it at the span of a week

Re: How to create a chart to show count of events by hour over days in a week?

woodcock — Sat, 30 Jun 2018 23:54:08 GMT

When you do a timechart it sorts the stack alphabetically; see this run-anywhere example:

index=_internal 
|  timechart count BY sourcetype

But you can add an extra line to resort, like this:

index=_internal 
|  timechart count BY sourcetype
| table _time splunk* mongo* *