Getting Data In

Average/Mean time differences by device

michaelhitzelbe
New Member

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 
0 Karma
1 Solution

whrg
Motivator

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 ...

View solution in original post

0 Karma

whrg
Motivator

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 ...
0 Karma

michaelhitzelbe
New Member

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.

0 Karma

michaelhitzelbe
New Member

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

0 Karma

ashajambagi
Communicator

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

0 Karma

michaelhitzelbe
New Member

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
0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...