I am new to splunk and I am ingesting data from smart lights from my home into splunk, I want to create dashboard to check how long every light was turned on for a given day. I am sort of stuck in figuring out when the state of a particular field changed.
my events look something like this:
{
"id": "***",
"uuid": "***",
"label": "Lamp",
"connected": true,
"power": "on",
"color": {
"hue": 33.997711146715496,
"saturation": 0,
"kelvin": 3500
},
"brightness": 0.6999923704890516,
"effect": "OFF",
"group": {
"id": "*",
"name": "*"
},
"location": {
"id": "*",
"name": "*"
},
"product": {
"name": "*",
"identifier": "*",
"company": "*",
"capabilities": {
"has_color": true,
"has_variable_color_temp": true,
"has_ir": false,
"has_chain": false,
"has_matrix": false,
"has_multizone": false,
"min_kelvin": 2500,
"max_kelvin": 9000
}
},
"last_seen": "2019-11-12T04:24:10Z",
"seconds_since_seen": 1
}
I was reading through splunk answers where people suggested using streamstats. I tried to start with it to query when the last change in my power field happened, here is my SPL:
index="lights" label="myLightName"
| streamstats current=t last(power) as LastPower last(_time) as timeChanged by label
| convert ctime(timeChanged)
| table LastPower, timeChanged
The table generated doesn't show me the time when a particular light(label) changed in state (power from on to off or vice versa). any ideas as to what I might be missing here?
Here are a few sample stats from the table generated:
@smucheli_splunk ,
Test this against your data
"base search"
|streamstats current=f last(power) as prev,last(_time) as prev_time window=1
|eval diff=abs(prev_time-_time)|eval status=if(power==prev AND power=="on",1,0)
|stats sum(eval(if(status==1,diff,0))) as on,sum(eval(if(status==0,diff,0))) as off
@smucheli_splunk ,
Test this against your data
"base search"
|streamstats current=f last(power) as prev,last(_time) as prev_time window=1
|eval diff=abs(prev_time-_time)|eval status=if(power==prev AND power=="on",1,0)
|stats sum(eval(if(status==1,diff,0))) as on,sum(eval(if(status==0,diff,0))) as off