Average/Mean time differences by device

[michaelhitzelbe]

I have data from several devices in the same index and sourcetype. I'd like to get the average/mean diffs for each specific device.

The data looks like this:

_time   device
2019-02-19 11:02:36 245
2019-02-19 11:02:36 245
2019-02-19 11:02:34 373
2019-02-19 11:02:34 234
2019-02-19 11:02:33 204
2019-02-19 11:02:33 204
2019-02-19 11:02:33 303
2019-02-19 11:02:33 303
2019-02-19 11:02:33 200
2019-02-19 11:02:33 381
2019-02-19 11:02:33 381
2019-02-19 11:02:32 271
2019-02-19 11:02:32 271
2019-02-19 11:02:30 080
2019-02-19 11:02:30 080
2019-02-19 11:02:30 181
2019-02-19 11:02:30 181
2019-02-19 11:02:28 081
2019-02-19 11:02:28 081
2019-02-19 11:02:28 301
2019-02-19 11:02:28 301
2019-02-19 11:02:28 156
2019-02-19 11:02:28 156
2019-02-19 11:02:27 316
2019-02-19 11:02:27 316
2019-02-19 11:02:27 363
2019-02-19 11:02:27 363
2019-02-19 11:02:27 262
2019-02-19 11:02:27 262

I'd like to get a table like this:

    device | average diff |mean diff
    245      |           23 |      25 
    381      |           17 |      21 
    245      |           31 |      19 

[whrg · Accepted solution]

You might need to be more specific as to how you calculate the average/mean difference for each device.

Based on your comment, perhaps this search can help you:

index=device_events | sort -_time | delta _time AS timeDeltaS | eval timeDeltaS=abs(timeDeltaS) | stats mean(timeDeltaS) as average_diff by device

EDIT: Okay, so based on your comment below, you might want to use streamstats instead, since delta does not support the by clause. Check this out:

index=device_events
| streamstats window=1 current=false global=false first(_time) as previous_time by device
| eval diff_s=previous_time-_time
| stats ...

[michaelhitzelbe]

Thanks. I tried that, too, but it does the "splitting" by device one step too late.
I would like to calculate the diff by device, so I would like something like ... delta _time by device

So if I have these events:
2019-02-20 8:00:00 deviceA
2019-02-20 8:00:10 deviceB
2019-02-20 8:00:20 deviceA
2019-02-20 8:00:30 deviceB

The search would result in mean/average time diffs of 10s for both, deviceA and deviceB.
What I really would like to have would be 20s, as the events of the respective devices are 20s apart.

[michaelhitzelbe]

Thanks, the version with streamstats works!
Thanks a lot!

[ashajambagi]

Can you explain in what reference you are calculating the diff?

[michaelhitzelbe]

It was just random numbers...

I can get the diff without respecting the devices by using this search

index=device_events | sort - _time | delta _time AS timeDeltaS | eval timeDeltaS=toString(abs(timeDeltaS),"duration") | table _time device timeDeltaS

which gives me something like this:

_time               device  timeDeltaS
2019-02-19 10:46:09 111  
2019-02-19 10:46:09 111 00:00:00
2019-02-19 10:46:08 112 00:00:01
2019-02-19 10:45:47 112 00:00:21
2019-02-19 10:44:47 112 00:01:00
2019-02-19 10:43:56 135 00:00:51
2019-02-19 10:43:47 112 00:00:09
2019-02-19 10:42:47 112 00:01:00
2019-02-19 10:41:47 112 00:01:00
2019-02-19 10:40:47 112 00:01:00
2019-02-19 10:40:40 135 00:00:07
2019-02-19 10:40:40 135 00:00:00
2019-02-19 10:40:39 135 00:00:01
2019-02-19 10:40:38 135 00:00:01
2019-02-19 10:40:38 135 00:00:00
2019-02-19 10:40:38 135 00:00:00
2019-02-19 10:40:38 135 00:00:00
2019-02-19 10:40:38 135 00:00:00
2019-02-19 10:40:38 135 00:00:00
2019-02-19 10:40:38 135 00:00:00
2019-02-19 10:40:37 135 00:00:01
2019-02-19 10:40:37 135 00:00:00
2019-02-19 10:40:37 135 00:00:00
2019-02-19 10:40:37 135 00:00:00
2019-02-19 10:40:06 135 00:00:31
2019-02-19 10:40:06 135 00:00:00
2019-02-19 10:40:06 135 00:00:00
2019-02-19 10:40:05 135 00:00:01
2019-02-19 10:40:05 135 00:00:00