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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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