How can I produce a timechart with 1 month span the average of count per day?

[morethanyell]

How can we produce a timechart (span is monthly) but the 2nd column is (instead of count of the events for that month) the average daily count of events during that month?

| timechart span=1mon count as Total
The code above will only count the number of events for, say "Jan 2018, Feb 2018, March 2018..." What I'm trying to achieve is something like this

| timechart span=1mon total_count_for_this_month_div_by_num_of_days_with_events_for_this_month

So, for example

Jan 1=10 events
Jan 3=12 events
Jan 14=15 events
Jan 21=6 events
total events=43
average=10.75

Feb 1=13 events
Feb 3=25 events
Feb 4=4 events
Feb 12=13 events
Feb 13=26 events
Feb 14=7 events
Feb 16=19 events
Feb 16=16 events
Feb 22=9 events
total events=132
average=14.67

Doing a | timechart span=1mon count as Total will result to

_time       count
2018-01      43
2018-02      132

What I'm trying to achieve is

_time       daily_avg
2018-01      10.75
2018-02      14.67

Thanks in advance!

[somesoni2]

How about this?

your search
| eval day=strftime(_time,"%F")
| timechart span=1m count as Total dc(day) as days | eval DailyAvg=Total/days

[FrankVl · Accepted solution]

Then you need to first get a daily count over time and then write your final timechart as follows:

| timechart avg(count) span=1mon

For example:

| tstats count where index=* by _time span=1d
| timechart avg(count) span=1mon

How exactly you get a daily count at first depends a bit on the rest of your query, so if you need some further help with that, please share what query you have before the timechart part.

[morethanyell]

question update. thanks!

[FrankVl]

Yes, that is what my suggested solution achieves. You just need to make sure that before you apply the timechart command for the monthly average, you have statistics with a count by day. And as mentioned: if you need help with that part of the query, you need to share what your query looks like before the | timechart.

[morethanyell]

Thank you. It worked like a charm.

| tstats count where index=foo sourcetype=bar by _time span=1d
| timechart span=1mon avg(count) as Total
| eval Total = round(Total, 2)
| fillnull value=0.00