How can I get accumulative values for a day for a period of time?

[northwarks]

One of the things I'm using Splunk to monitor is electricity usage, one of the fields indexed is the accumulative Kw value for the day, how can I get the last value for the day for a given timespan? So output the total Kw for each day for a month - I've tried using

host=Electricity earliest=-4w@w1 latest=+w@w1 | timechart last(live_day_kw) as Kw

but for the data I have it seems to be adding each day together to its increasing day by day and not daily values, so for example day1 is 7kw and day2 is 14kw and day3 is 21kw - I'd expect it to be ~7kw a day.

[northwarks]

Guys, thanks for the inputs - I've sussed this, the following gives me the final value of the day for each day rounded down to 2 decimal places:

host=Electricity earliest=-4w@w1 | timechart latest(live_day_kw) as "Kw_Day" | eval Kw_Day = round(Kw_Day,2)

I'll keep it running for a few days to see if it gives consistent results and tweak it from there ..

[niketn]

@northwarks once you find this working don't forget to Accept your own answer to mark this question as answered. Do up vote the comments from others which helped 🙂

[ralzate]

Hello

If you have multi values per days :
You can combine with the "first" like this

host=Electricity earliest=-4w@w1 latest=+w@w1
| timechart span=1d first(live_day_kw) as maxi  last(live_day_kw) as mini
| eval kw=maxi - mini
| table _time kw

If you have only one value per day :

host=Electricity earliest=-4w@w1 latest=+w@w1
| eval _time=relative_time(_time,"-0d@d")
| join type=inner _time [ host=Electricity earliest=-4w@w1 latest=+w@w1
      | eval _time=relative_time(_time,"-1d@d")
      | rename live_day_kw as live_day_kw_before]
| timechart span=1d max(live_day_kw_before) as mini max(live_day_kw) as maxi
| eval kw=maxi - mini
| table _time kw

Or something like that. It doesn't work if there is missing value

Regards
Régis

[somesoni2]

Give this a try

host=Electricity earliest=-4w@w1 latest=+w@w1
| fields _time live_day_kw
| bucket span=1d _time 
| dedup _time

[splunker12er]

Try if this works

host=Electricity earliest=-4w@w1 latest=+w@w1 | timechart span=1d sum(live_day_kw) as Daily_Kw

[niketn]

@northwarks, you can apply delta command on Kw

host=Electricity earliest=-4w@w1 latest=+w@w1 
| timechart last(live_day_kw) as Kw
| delta Kw as "Kw (per span)"
| eval "Kw (per span)"=if(isnull('Kw (per span)'),Kw,'Kw (per span)')
| fields - Kw

Following is a run anywhere search to test out the same:

| makeresults
| eval data="time=\"2018-08-01\",Kw=\"7\";time=\"2018-08-02\",Kw=\"16\";time=\"2018-08-03\",Kw=\"21\""
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| eval _time=strptime(time,"%Y-%m-%d")
| table _time Kw
| delta Kw as "Kw (per span)"
| eval "Kw (per span)"=if(isnull('Kw (per span)'),Kw,'Kw (per span)')
| fields - Kw

[northwarks]

Hi - Thanks for the quick reply, this doesn't work for me as its not the delta I'm after but the final total (very last value) for live_day_kw for each day over a week or month?

30.03.2018 - live_day_kw =16.60143805554
31.03.2016 - live_day_kw =18.48926583334

host=Electricity earliest=-4w@w1 latest=+w@w1 | timechart span=1d latest(live_day_kw) as Kw

is the closest I've got, it returns today OK but not 16.601 for the 30.03.2018

[niketn]

@northwarks , can you give the example of what you have and what you want? Also what your raw event look like?

Instead of latest() have you tried earliest(), last() or first() to see if returns what you need?

Let me convert this answer to comment so that it flags as unanswered for others to assist as well.