topic Re: Duration of two events by status! in Splunk Search

Duration of two events by status!

reschal — Mon, 27 Nov 2017 14:11:25 GMT

hi,
my raw data look like this:

12:01:11:000 ip: "123.456.789" = "1"
12:01:12:000 ip: "123.456.789" = "1"
12:01:13:000 ip: "123.456.789" = "1"
12:01:14:000 ip: "123.456.789" = "2"
12:01:15:000 ip: "123.456.789" = "2"
12:01:16:000 ip: "123.456.789" = "1"
12:01:17:000 ip: "123.456.789" = "1"

Now i want to calculate the duration only between the first event of "1" and "2". In other words, the duration when the ip shows the status "1". I tried some transaction and streamstats commands but nothing worked properly.

Thanks for your help!

Re: Duration of two events by status!

damien_chillet — Mon, 27 Nov 2017 15:46:01 GMT

You could try with the transaction command:

index=<your_index> sourcetype=<your_sourcetype>
| transaction ip endswith=value=2
| eval keep=mvfilter(match(value, "1"))
| where keep=1

Assuming you are extracting the ip under field "ip" and values 1/2 under field "value"

Re: Duration of two events by status!

somesoni2 — Mon, 27 Nov 2017 16:14:29 GMT

You can probably do this by using reverse and dedup command. Are both ip and status an extracted fields? If yes, something like this would work.

your base search | reverse | dedup ip status | stats range(_time) by ip

Re: Duration of two events by status!

lycollicott — Mon, 27 Nov 2017 20:00:38 GMT

(I loaded your sample data into my test box then I did a field extraction for the IP and another for CODE.)

index=main sourcetype=reschal2 | sort _time
| table _time, CODE
| streamstats range(_time) as DURATION by CODE reset_on_change=true

Those commands will show you how long it was "1", then "2" then "1" again.

Here are some screenshots....

Re: Duration of two events by status!

lycollicott — Mon, 27 Nov 2017 20:22:08 GMT

Don't know why the screenshot isn't displaying and the site won't let me upload another. 😞

Re: Duration of two events by status!

somesoni2 — Mon, 27 Nov 2017 20:51:29 GMT

I would change | sort _time to | sort 0 _time in order to avoid it to be truncated after 10K rows (default limit).

Re: Duration of two events by status!

reschal — Tue, 29 Sep 2020 17:01:22 GMT

Thank you. Your answer works great. By adding

|table _time duration |eval duration=tostring(duration,"duration")| sort -_time

i get a proper solution!

Re: Duration of two events by status!

damien_chillet — Tue, 28 Nov 2017 09:36:15 GMT

Yea, i forgot to mention duration would be stored under duration field, that's great you found it!

Thanks for accepting my answer and happy Splunking!