Splunk Search

How do I find the time period when my events were in a particular state

smucheli_splunk
Splunk Employee
Splunk Employee

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:
alt text

0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@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

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

@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

View solution in original post

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.