Calculate the percentage of a given date_hour

[patrick_cheung]

I have the following data set with says 1000+ data:
Time, Duration in hours, eg.
13:23 2018-2-3, 0.234
15:13 2018-3-14, 0.123
...
Assume I know that the peak hours are 3am to 3pm, I would like to visualize the data in monthly timespan for the consumption,
aka. sum of "Duration in hours" between 3am and 3pm out of sum of "Duration in hours" in whole day. Any hints on this? Thanks a lot!
Eg.
Jan 2018, 30%
Feb 2018, 35%
Mar 2018, 40%

[strive · Accepted solution]

There are two ways:
Option1: Include date_year also in stats and then join them.
Something like: index=_internal | stats count by date_month date_year | eval Month=date_month." ".date_year | fields Month count

Option 2: Assuming that your data is properly indexed with timestamp, do eval on _time field.
Something like: index=_internal | eval Month=strftime(_time, "%Y %m") | stats count by Month

[p_gurav]

Hi Try something like this:

index="you_index_name" | stats sum(duration_in_hours) AS total by date_month | appendcols [search index="you_index_name" date_hour > 3 AND date_hour < 15 | stats sum(duration_in_hours) AS select by date_month ] | eval percentage=round(select/total*100) | fields date_month, percentage

[patrick_cheung]

Thanks @p_gurav , any hints instead of displaying date_month (aka december, january, february etc), how to display a more meaningful time series like 2017-12, 2018-01 (or December 2017, January 2018 etc)?
i tried to use replace with them but this method cant distinguish between december 2017 and december 2018.

Thanks again!