Hi
I need help with some home automation energy usage. I have a device that sends watt usage every 10 seconds to my Splunk. From this I calculate the difference between each reading like in the table below.
watt
5
-15
2
-5
3
6
640
-4
-2
15
From this I do some eval like this:
eval case(watt < 680 AND watt > 620,"heater_on",1=1,"other")
This gives me that the heater is turning on.
Problem are when reading goes over more than one line like the table below:
Question is how to I use Splunk to sum some nearby rows so I get the slow changing devices?
watt
5
-15
2
-5
3
6
138
505
-4
-2
15
Based on final requirements, try this:
| makeresults
| eval watt="5 -15 2 -5 3 6 138 505 -4 -2 15"
| makemv watt
| mvexpand watt
| streamstats count AS _offset
| eval _time = _time - _offset
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| eval watt=if(watt<=20, 0, watt)
| streamstats count(eval(watt==0)) AS sessionID
| stats count min(_time) AS _time sum(watt) AS watt list(watt) AS watts BY sessionID
| eval status = case(watt < 680 AND watt > 620, "heater_on", true(), "other")
Based on final requirements, try this:
| makeresults
| eval watt="5 -15 2 -5 3 6 138 505 -4 -2 15"
| makemv watt
| mvexpand watt
| streamstats count AS _offset
| eval _time = _time - _offset
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| eval watt=if(watt<=20, 0, watt)
| streamstats count(eval(watt==0)) AS sessionID
| stats count min(_time) AS _time sum(watt) AS watt list(watt) AS watts BY sessionID
| eval status = case(watt < 680 AND watt > 620, "heater_on", true(), "other")
This worked perfect. Thank for your time and effort to solve this 🙂
With help form some other I did get a working solution.
It's not perfect, since the time would be set to laste event before change of watt. This can be fixed by adding 10 second or try to get the time form event that first comes with a change:
Added some more data to my list just to see that it works:
| makeresults
| eval watt="5 -15 2 -5 3 6 138 405 100 -4 68 2 15 -640"
| makemv watt
| mvexpand watt
| streamstats count AS _offset
| eval _time = _time - _offset*10
| rename COMMENT AS "Everything above generates sample event data; everything below is the solution"
| eval watt=if(watt>-20 AND watt<20,0,watt)
| eval flag=if(watt<20 AND watt>-20,_time,null())
| filldown flag
| stats sum(watt) as watt count by flag
| eval flag=strftime(flag,"%d:%m:%y %H:%M:%S")
Result would gives this list (I could remove all the 0, but keep it to show the output, same with count, could be removed)
flag watt count
01:04:20 14:05:29 -640 2
01:04:20 14:05:39 0 1
01:04:20 14:05:59 68 2
01:04:20 14:06:39 643 4
01:04:20 14:06:49 0 1
01:04:20 14:06:59 0 1
01:04:20 14:07:09 0 1
01:04:20 14:07:19 0 1
01:04:20 14:07:29 0 1
Something like this:
| makeresults
| eval watt="5 -15 2 -5 3 6 138 505 -4 -2 15"
| makemv watt
| mvexpand watt
| streamstats count AS _offset
| eval _time = _time - _offset
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| streamstats window=3 sum(watt) AS wattsum
| eval status = case(wattsum < 680 AND wattsum > 620,"heater_on", true(), "other")
Thanks for the reply. This also gives two times heater on, My goal is to get heater_on when on or two events gives sum of around 640.
It sums events before or after all events, not joining them if they are for example larger than 20 watt
So change it to widow=2
.
Better, this gives only one "heater_on", but lets say there is a device that uses 500 watt. It will then trigger on this solution since we get these values: "5 -10 -13 -3 -2 9 144 643 501 -6 13"
With my human eye, I see that this group belongs together. Should be a possibility to see that there is a raise in value, and if next value are larger than example 20w, it belongs to previous line and should be looked at as one line.
Also if value raise in one go, like this: | eval watt="5 -15 2 -5 3 6 640 -4 -2 15"
, it will show two heater_on due to the summing of fields.
Get your algorithm fully defined and then we can help. There is no point in me answering your questions and then you redefining your requirements so that the answer is no longer valid. This game is not any fun for me.
Algorithm is very simple. Number larger than 20 should be taken inn to account. Rest could be set to 0 or ignored as no change. Number larger than 20 that comes one after another should be seen as one evnet. Joined together and summed. Then we can test that value against a lost of parameters to see what did changed. It can be on number like 640 watt, it can go over two lines like 135 watt and 505 watt. To make it complex it can even go over several lines like 180 watt, 200 watt and then 260 watt. But from what I have seen 99% of the devises creates one or two lines of watt changes. If its one or more lines, all lines larger than 20 should be summed. From my first post it shows clearly the two most common scenario. With change over on or two lines. Both should give same result. One line with heater_on. Thanks for trying to help out.
Perfect. Now see my new answer.
|makeresults
| eval _raw="watt
5
-15
2
-5
3
6
138
505
-4
-2
15"
| multikv forceheader=1
| eval watt=if(watt <= 20,0,watt)
|stats sum(watt) as calc_watt
| eval result=case(calc_watt < 680 AND calc_watt > 620,"heater_on",calc_watt >= 500,"vacuum_cleaner",1=1,"other")
This is different from first question .
This will fail as the same way as jpolvino posts.
If value in -3 6 640 5 -2 etc you will get two heater_on.
Should be a way to sum all values above a certain limit and make them to be as one line.
what's certain limit
?
Example all variation less than 20 should be set to 0. Then all value above this should be treated as one if they are together. Eks 640 = heater, 500=vacuum cleaner. Then 138 + 505 should only be seen as heater and not two heater or one heater and one vacuum cleaner.
Without more info, here is one potential solution, using "Run Anywhere" SPL:
| makeresults | eval raw2=split("watt=5,watt=-15,watt=2,watt=-5,watt=3,watt=6,watt=138,watt=505,watt=-4,watt=-2,watt=15",",") | mvexpand raw2 | eval _raw=raw2 | extract | fields - _raw raw2 | table watt
| autoregress watt p=1
| eval wattSum=watt+watt_p1
| eval appliance=if(wattSum>620 AND wattSum<680,"Probably heater","something else")
This uses the autoregress
command to enable nearby rows to be examined. Then using your criteria, indicate which one is probably your heater. The fields are shown in the output for completeness.
This may work, but still leaves side values that could be miss understand as other devices.
If you only have one high value with watt=640, and the other around is low, then you will get two probably_heater, since you get two values nearly equal. I did get this result as well on a much more complicated formula 🙂
How do you know that the wattage reading belongs to a certain appliance? Also, what do the negative readings indicate?