All Apps and Add-ons

How to I get the total amount accum per day, yesterday and last week ?


My problem is:

I need to accumulate one day of data per hour on a timeline. Eg.
DayTime Amount
12/02/2014 17:00 10
12/02/2014 18:00 11
12/02/2014 19:00 15
13/02/2014 20:00 30
13/02/2014 21:00 90
14/02/2014 20:00 20
14/02/2014 21:00 100

Add columns: Now (Tuesday) , yesterday (Wednesday) and Last Week(Tuesday). They are all accumulated values.

I am try

index="prod" earliest=-7d@d latest=-5s

| timechart span=1h sum(amount) as total

| timewrap d series=short
| accum total_s0 as now
| accum total_s1 as yesterday
| accum total_s7 as last_week
| table _time, _span, now, yesterday, last_week

, but the values of total_s0 accumulated 7 days, totaled more than necessary.

Tags (3)

Splunk Employee
Splunk Employee

If I understood the question right, this is the exact same question as before, except instead of count we're using 'sum'. Note, I renamed the sum(spent) as count to make the naming easier. I literally didn't change anything. This search is superior, I believe, to the other answers, because it only does one search and doesn't involve any math or subsearches...

index="prod" earliest=-7d@d latest=-5s 
| timechart sum(spent) as count span=1h 
| timewrap  d series=short 
| addtotals s*
| eval 7dayavg=Total/7.0
| table _time, _span, s0, s1, 7dayavg
| rename s0 as now, s1 as yesterday

Summary: get the data, make the timechart, wrap the data around a day, get the totals for each time period, so we can get the average by dividing by 7. Then clean up the field names.

P.S. If you don't want the average accumulated for the week, just skip the divide by 7 step.

0 Karma




| eval _time=_time+(24*60*60)

0 Karma

Splunk Employee
Splunk Employee

one option is to use sub searches for eachtime range, and edit the time to put them on the same day.

index="prod" earliest=-1d@d latest=now | eval serie="today"
| append [ index="prod" earliest=-2d@d latest=-1d@d | eval serie="yesterday" | eval _time=_time+(24*60*60) ]
| append [ index="prod" earliest=-7d@d latest=-6d@d | eval serie="last_week" | eval _time=_time+(7*24*60*60) ]
| timechart span=1h sum(amount) by serie



I did this way:

index="prod" earliest=@d latest=-5s

| eval Hora=strftime(_time,"%H")

| stats sum(corrente) as tday by Hora
| appendcols

[ search index="prod" earliest=-1d@d latest=@d
| eval Hora=strftime(_time,"%H")
| stats sum(corrente) as yday by Hora]
| appendcols

[ search index="prod" earliest=-7d@d latest=-6d@d
| eval Hora=strftime(_time,"%H")
| stats sum(corrente) as lweek by Hora]
| accum tday as today
| accum yday as yesterday
| accum lweek as last_week
| table Hora, _span, today, yesterday,last_week

0 Karma