Duration of two events by status!

[reschal]

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!

[lycollicott]

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

[lycollicott]

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

[somesoni2]

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

[somesoni2]

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

[damien_chillet · Accepted solution]

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"

[reschal]

Thank you. Your answer works great. By adding

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

i get a proper solution!

[damien_chillet]

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!