Splunk Search

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

morethanyell
Builder

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!

1 Solution

FrankVl
Ultra Champion

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.

View solution in original post

somesoni2
Revered Legend

How about this?

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

FrankVl
Ultra Champion

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
Builder

question update. thanks!

0 Karma

FrankVl
Ultra Champion

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.

0 Karma

morethanyell
Builder

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
0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...