topic Re: subtract meterRead from Yesterday’s meterRead in Splunk Search

subtract meterRead from Yesterday’s meterRead

myron12 — Thu, 07 May 2020 16:19:41 GMT

My electric meter sends a number but I want to subtract the current from the number an hour ago, so I can chart the usage for each hour.

My search:
source="/home/we/plex/movies/meter.elec" _time=* LastConsumptionCount=*
| table _time "LastConsumptionCount"

Re: subtract meterRead from Yesterday’s meterRead

richgalloway — Thu, 07 May 2020 17:22:14 GMT

If you have one event an hour, try delta.

source="/home/we/plex/movies/meter.elec" LastConsumptionCount=*
`comment("Calculate the difference between this event and the previous one")`
| delta LastConsumptionCount as Usage
`comment("Plot the values over time.")`
| timechart span=1h max(Usage) as Usage

If you have multiple events an hour, then this may work better.

source="/home/we/plex/movies/meter.elec" LastConsumptionCount=*
`comment("Group events by hour")`
| bucket span=1h _time
`comment("Get the first(min) and last(max) values for each bucket")`
| stats min(LastConsumptionCount) as first, max(LastConsumptionCount) as last by _time
`comment("Hourly use is the difference between the two readings")`
| eval Usage = last- first
| timechart span=1h max(Usage) as Usage

Re: subtract meterRead from Yesterday’s meterRead

shivanshu1593 — Thu, 07 May 2020 17:30:29 GMT

Assuming your current and the last consumption cound from an hour ago are stored inside the same field called LastConsumptionCount, you can try something like this:

source="/home/we/plex/movies/meter.elec"  | streamstats current=t time_window=1h window=1 last(LastConsumptionCount) as prev_ConsumptionCount | eval difference = LastConsumptionCount - prev_ConsumptionCount | timechart span=1h max(difference)

Re: subtract meterRead from Yesterday’s meterRead

myron12 — Thu, 07 May 2020 17:58:59 GMT

I do have multiple events per hour and your multiple example works up to line 7 and returns one value.
That value isn't from the bucket but from the search time span.

I appriciate the help.

Re: subtract meterRead from Yesterday’s meterRead

richgalloway — Thu, 07 May 2020 18:33:03 GMT

Critical omission on line 5 fixed.

Re: subtract meterRead from Yesterday’s meterRead

myron12 — Thu, 07 May 2020 18:45:46 GMT

Maybe my problem is that the variable "LastConsumptionCount" is a single number. That is the number that it says on the meter. Not a variable array.

I store the meter reading every 30 seconds.

Re: subtract meterRead from Yesterday’s meterRead

myron12 — Thu, 07 May 2020 19:00:13 GMT

Yes that is it. Thanks.

Re: subtract meterRead from Yesterday’s meterRead

myron12 — Thu, 07 May 2020 19:55:39 GMT

The bucket thing works with one problem.
So I get one bucket has a first value and a last value.
the next bucket first value isn't the same number as the last value of the previous.

I would like to have the first value subtracted from the previous first value.

It seams like I am 'going the long way around the barn'. and I would have expected timechart would have a way to handle this.

Re: subtract meterRead from Yesterday’s meterRead

shivanshu1593 — Thu, 07 May 2020 20:46:37 GMT

After reading your comments on Rich's answer, I think I got your requirement. Let's say that you ran this search at 3 PM, and now want to compare the value of LastConsumptionCount at 3 PM with it's value, which you got at 2 PM, exactly 1 hour back. Am I correct? If so, then try this:

source="/home/we/plex/movies/meter.elec" earliest=-1h latest=now()  | streamstats current=t time_window=1h window=1 last(LastConsumptionCount) as prev_ConsumptionCount | eval difference = LastConsumptionCount - prev_ConsumptionCount | timechart span=1h max(difference) as difference