Duration determination

[roayers]

Here is a sample of a connection that spans the midnight hour into the next day. I'm trying to extract a new field named duration that contains the difference between the connect and disconnect times in a human readable format

1547514148 2019-01-14 20:02:27 connect johndoe
1547530430 2019-01-15 00:33:49 disconnect johndoe

I've tried many different splunk commands such as stats, eval and transaction commands from many posts here but have been unsuccessful

Thanks in advance,
Robert

[whrg]

Hello Robert,

I assume you have the fields _time, action (connect|disconnect) and username available; or named something similar.

If you are missing the _time field, then use the strptime() eval function to create it.

Now I suggest you use the transaction command as follows:

your base search
| transaction user startswith=(status="connect") endswith=(status="disconnect")
| eval duration=tostring(duration, "duration")

The transaction command will create the duration field.

The eval command will convert the duration from the number of seconds to the more human readable format HH:MM:SS.

[roayers]

This worked after I changed the field name status to action

status="connect") endswith=(status="disconnect") to this
action="connect") endswith=(action="disconnect")

[woodcock]

Like this (assuming user is the field that owns johndoe😞

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo
| streamstats count(searchmatch("disconnect")) AS sessionID BY user
| stats range(_time) AS duration BY sessionID user

And then maybe also

| eval duration = tostring(duration, "duration")

[roayers]

This generated the following error
Error in 'SearchProcessor': Mismatched quotes and/or parenthesis.

[woodcock]

I was missing a ). I updated my answer. Try it now, @roayers.