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
Happy Splunking!

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
Happy Splunking!
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...