All Apps and Add-ons

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

agentelinux
Explorer

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)

carasso
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

agentelinux
Explorer

Humm,

why?

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

0 Karma

yannK
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

agentelinux
Explorer

Yannk,

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...