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
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.
This worked after I changed the field name status to action
status="connect") endswith=(status="disconnect") to this
action="connect") endswith=(action="disconnect")
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")
This generated the following error
Error in 'SearchProcessor': Mismatched quotes and/or parenthesis.
I was missing a )
. I updated my answer. Try it now, @roayers.