topic Re: Comparing sum results between several days in Splunk Search

Comparing sum results between several days

KaitoKozo — Wed, 27 Jan 2021 03:28:40 GMT

I am trying to average the sum of power consumption readings between 2 days and compare that sum to a 3rd day. If the 3rd day’s total power consumption is 20% higher than the average of the previous 2 days, I would like to flag the day as having more power consumption than usual.

The main issue I have is in trying to do this comparison as I’m unsure if it’s possible to store data as variables similar to programming and am unable to do the full search/compute/compare in 1 line, in particular trying to target “specific dates relative to current date”.

I am having difficulty trying to implement my logic process in splunk as I am still relatively new to the system and am unsure about the capabilities and syntax of this platform.

Re: Comparing sum results between several days

scelikok — Wed, 27 Jan 2021 04:19:15 GMT

Hi @KaitoKozo,

Please try below;

index=power_consumption earliest=-3d@d | timechart span=1d sum(consumption) as consumption | streamstats window=2 current=f avg(consumption) as last2days | tail 1 | where last2days>0 AND consumption>last2days*1.2

Re: Comparing sum results between several days

KaitoKozo — Thu, 28 Jan 2021 03:28:13 GMT

Thanks! This has helped a bunch! However, I'm now getting an error that it has the '>' operator received different types in the where command.

Through some testing, I have found out that the comparison of "Consumption > last2days*1.2" is the one causing issues, in particular "last2days*1.2". I am able to run the code if I change it to "Consumption > last2days", however the final result that appears is that Consumption is lesser than last2days.

This is the SS of the results that showed up when I added your modifications with "Consumption > last2days" instead.

Edit: I just digested and understood what the code does, so that 1 result was actually supposed to appear. Is there any way to create a chart that will plot those situations that only have days where the consumption is higher than the previous 2 days?