I currently have a log of json-formatted events that shows the changing value for several different IDs, like this:
[{"fields": {"value": 1}, "measurement": "light", "tags": {"entity_id": "lightA", "domain": "light"}, "time": "2016-01-21 11:52:04+00:00"}]
[{"fields": {"value": 1}, "measurement": "light", "tags": {"entity_id": "lightB", "domain": "light"}, "time": "2016-01-21 11:52:09+00:00"}]
[{"fields": {"value": 1}, "measurement": "light", "tags": {"entity_id": "lightC", "domain": "light"}, "time": "2016-01-21 11:52:09+00:00"}]
[{"fields": {"value": 0}, "measurement": "light", "tags": {"entity_id": "lightB", "domain": "light"}, "time": "2016-01-21 12:04:54+00:00"}]
[{"fields": {"value": 0}, "measurement": "light", "tags": {"entity_id": "lightA", "domain": "light"}, "time": "2016-01-21 12:04:53+00:00"}]
[{"fields": {"value": 0}, "measurement": "light", "tags": {"entity_id": "lightC", "domain": "light"}, "time": "2016-01-21 12:04:59+00:00"}]
[{"fields": {"value": 1}, "measurement": "light", "tags": {"entity_id": "lightA", "domain": "light"}, "time": "2016-01-21 12:15:57+00:00"}]
[{"fields": {"value": 1}, "measurement": "light", "tags": {"entity_id": "lightB", "domain": "light"}, "time": "2016-01-21 12:15:58+00:00"}]`
I have the sourcetype defined so that I can pull the fields out into a table like this:
index = * | table _time , entity_id , value
_time entity_id value
2016-01-21 06:52:04 lightA 1
2016-01-21 06:52:09 lightB 1
2016-01-21 06:52:10 lightC 1
2016-01-21 07:04:54 lightB 0
2016-01-21 07:04:54 lightA 0
2016-01-21 07:04:59 lightC 0
2016-01-21 07:15:57 lightA 1
2016-01-21 07:15:58 lightB 1
I want to create a table that includes the length of time each ID was that value (where the last one is considered to end at the current time), like this:
_time entity_id value duration
2016-01-21 06:52:04 lightA 1 770
2016-01-21 06:52:09 lightB 1 765
2016-01-21 06:52:10 lightC 1 769
2016-01-21 07:04:54 lightB 0 664
2016-01-21 07:04:54 lightA 0 663
2016-01-21 07:04:59 lightC 0 9416
2016-01-21 07:15:57 lightA 1 8776
2016-01-21 07:15:58 lightB 1 8775
I've tried several approaches to using the transaction command for this (both on the initial search itself and on the table), but none of them are giving me the result I'm looking for, and I'm not sure if it's because I'm just misunderstanding how to use transaction, or if I'm using the wrong tool for the job. I've also tried variations of streamstats, with an equal lack of success. Can anyone point me in the right direction?
... View more