Splunk Search

subtract meterRead from Yesterday’s meterRead

Explorer

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"

Tags (2)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

Contributor

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)

Explorer

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.

0 Karma

Contributor

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
0 Karma

SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

Explorer

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.

0 Karma

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

Critical omission on line 5 fixed.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Explorer

Yes that is it. Thanks.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!