topic Re: How to create an accumulated time difference column that, upon reaching a certain value, resets to the current row's time difference? in Splunk Search

How to create an accumulated time difference column that, upon reaching a certain value, resets to the current row's time difference?

Joni123 — Thu, 26 Mar 2015 13:01:17 GMT

Hi,

I'm looking for a way to add an accumulated time difference column - but one that will "zero" every time it reaches a certain value (in this case, 2)
I'm looking for a command (or set of commands...) that will run automatically and offer indefinite "zeroing" events.

The current search I have is:

| sort 0 uuid _time
| streamstats current=f last(_time) as last_time by user_id session_id

| eval diff=(_time-last_time)/60
| streamstats current=t sum(diff) as accum_diff by user_id session_id

| table _time user_id session_id _time last_time diff accum_diff

And the result is:

_time   |user_id|session_id|last_time | diff |accum_diff
10:35:01|   1   |    1A    |          |      |
10:39:49|   1   |    1A    |1427304901|  4.8 |  4.8
10:39:50|   1   |    1A    |1427305189| 0.02 | 4.82
10:41:19|   1   |    1A    |1427305190| 1.48 |  6.3
10:41:25|   1   |    1A    |1427305279|  0.1 |  6.4
10:41:56|   1   |    1A    |1427305285| 0.52 | 6.92
10:42:43|   1   |    1A    |1427305316| 0.78 |  7.7
10:43:13|   1   |    1B    |          |      |
10:43:52|   1   |    1B    |1427305393| 0.65 | 0.65
10:43:53|   1   |    1B    |1427305432| 0.02 | 0.67
10:43:55|   1   |    1B    |1427305433| 0.03 |  0.7
10:44:19|   1   |    1B    |1427305435|  0.4 |  1.1
10:44:23|   1   |    1B    |1427305459| 0.07 | 1.17
10:44:25|   1   |    1B    |1427305463| 0.03 |  1.2
10:45:13|   1   |    1B    |1427305465|  0.8 |    2
08:01:13|   2   |    1B    |          |      |
08:01:30|   2   |    2A    |1427295673| 0.28 | 0.28
08:02:25|   2   |    2A    |1427295690| 0.92 |  1.2
08:02:41|   2   |    2A    |1427295745| 0.27 | 1.47
08:03:15|   2   |    2A    |1427295761| 0.57 | 2.03
08:03:56|   2   |    2A    |1427295795| 0.68 | 2.72
08:05:47|   2   |    2A    |1427295836| 1.85 | 4.57
08:05:55|   2   |    2A    |1427295947| 0.13 |  4.7
08:06:15|   2   |    2A    |1427295955| 0.33 |  5.03
08:06:34|   2   |    2A    |1427295975| 0.32 |  5.35
08:07:00|   2   |    2A    |1427295994| 0.43 |  5.78

How can I zero "accum_diff" and have it accumulate "diff" once it hit 2?

Thanks!

Re: How to create an accumulated time difference column that, upon reaching a certain value, resets to the current row's time difference?

vganjare — Tue, 28 Apr 2015 07:11:29 GMT

Can you please provide the expected output?

Thanks!!

Re: How to create an accumulated time difference column that, upon reaching a certain value, resets to the current row's time difference?

Joni123 — Wed, 29 Apr 2015 07:20:20 GMT

Yes - should look like this - when accum_diff=2, it zeros and starts the sum again in the next record:

_time |user_id|session_id|last_time | diff |accum_diff_max_2
10:35:01| 1 | 1A | | |
10:39:49| 1 | 1A |1427304901| 4.8 | 4.8
10:39:50| 1 | 1A |1427305189| 0.02 | 0.02
10:41:19| 1 | 1A |1427305190| 1.48 |1.5
10:41:25| 1 | 1A |1427305279| 0.1 | 1.6
10:41:56| 1 | 1A |1427305285| 0.52 | 2.12
10:42:43| 1 | 1A |1427305316| 0.78 | 0.78
10:43:13| 1 | 1B | | |
10:43:52| 1 | 1B |1427305393| 0.65 | 0.65
10:43:53| 1 | 1B |1427305432| 0.02 | 0.67
10:43:55| 1 | 1B |1427305433| 0.03 | 0.7
10:44:19| 1 | 1B |1427305435| 0.4 | 1.1
10:44:23| 1 | 1B |1427305459| 0.07 | 1.17
10:44:25| 1 | 1B |1427305463| 0.03 | 1.2
10:45:13| 1 | 1B |1427305465| 0.8 | 2
08:01:13| 2 | 1B | | |
08:01:30| 2 | 2A |1427295673| 0.28 | 0.28
08:02:25| 2 | 2A |1427295690| 0.92 | 1.2
08:02:41| 2 | 2A |1427295745| 0.27 | 1.47
08:03:15| 2 | 2A |1427295761| 0.57 | 2.03
08:03:56| 2 | 2A |1427295795| 0.68 | 0.68
08:05:47| 2 | 2A |1427295836| 1.85 | 2.53
08:05:55| 2 | 2A |1427295947| 0.13 | 0.13
08:06:15| 2 | 2A |1427295955| 0.33 | 0.46
08:06:34| 2 | 2A |1427295975| 0.32 | 0.78
08:07:00| 2 | 2A |1427295994| 0.43 | 1.21

Re: How to create an accumulated time difference column that, upon reaching a certain value, resets to the current row's time difference?

vganjare — Wed, 29 Apr 2015 08:50:13 GMT

Hi,

You can use custom search command for getting desired result. The custom search command is a python script which will get access to all the data result. Using small python code, this functinality can be achieved. More details @ http://docs.splunk.com/Documentation/Splunk/6.2.2/AdvancedDev/Searchscripts