How to search the duration between two events by field?

[averyml]

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?

[yannK · Accepted solution]

transaction may be the tool. but a streamstats can also be less expensive.

see http://docs.splunk.com/Documentation/Splunk/6.3.2/SearchReference/Transaction

simple example : (you may want to add some rules to specify what is a first ecnt

    <mysearch> | transaction source host mycommonfield maxevents=2 | table _time duration mycommonfield _raw

or http://docs.splunk.com/Documentation/Splunk/6.3.2/SearchReference/Streamstats

    <mysearch> | streamstats range(_time) as Duration window=2

[averyml]

* |streamstats range(_time) as Duration window=2 gives me the time between each event, but not the time between each event, per entity_id. I had tried * |streamstats range(_time) by entity_id as Duration window=2 before, and I thought it didn't work because there was no resulting Duration field, but I just realized that * |streamstats range(_time) does work, I just can't use the "by" clause with the "as" clause or the window. This seems to give me the difference between the time of each event and the last event of that entity_id. Another question/answer here makes it sound like global=f is what I want, but this:

` |sort -_time| streamstats range(_time) by entity_id| table _time, entity_id, value, range(_time)

and this:

|sort -_time| streamstats global=f range(_time) by entity_id| table _time, entity_id, value, range(_time)

give me the same result. Is there something wrong with my syntax?

[averyml]

oh, I need both global=f and window=2.

this works:
*|sort -_time| streamstats range(_time) by entity_id window=2 global=f| table _time, entity_id, value, range(_time)

or, with the duration adjustment:
*|sort -_time| streamstats range(_time) by entity_id window=2 global=f| table _time, entity_id, value, range(_time)|rename range(_time) as duration|eval duration=if(duration==0,now()-_time,duration)

Thank you!

[somesoni2]

Give this a try

index=I_suggest_You_Specify_IndexName_Here | table _time , entity_id , value | sort entity_id,_time | streamstats window=1 current=f values(value) as prev values(_time) as prev_time by entity_Id | where isnotnull(prev) | eval duration=_time-prev_time | table entity_id prev duration | rename prev as value

[averyml]

This is so very very close (I did have to add prev_time as a field to the table, but that was trivial) - but using this methodology, I can't figure out how to show the last transition. What I want is a kind of eval duration=if(,_time-prev_time, now()-_time) - except that doesn't seem to work in this case. I have 12 events, but the table ends up showing 9.