Splunk Search

How to calculate duration of a session to populate a summary index?

Contributor

Background:
My data is being sent to a summary index.
The search that populates the summary index is:

index=test1 transaction vendor_session_id startswith="New session" endswith="Session deleted" | collect index=vendor-traffic-grp1

** I've tried removing the statswith/endswith

The Goal:
I need to simply calculate the duration of the session so I can throw it to a panel/table/dashboard/etc for all/any sessions I need to display... username, session, duration

====================================
I have read numerous posts, documents and tried a multitude of search methods: stats*, eval, event*, _time, transaction.. But I am unable to generate duration.

The transaction command builds the duration field which is correct. I've tried to run the transaction again in the summary index, but the duration is zero or off by a few seconds so I have attempted to calculate the session duration using other search commands with no luck. It almost seems like pushing the data into the summary index wipes the original timestamp. I was thinking its a timestamp parsing issue ( prefix the <141> and (express the time) and lookahead 21'ish) but I am unable to change the timestamp in the summary index sourcetype "Stash" wish is autocreated when using the summary index. Any advice or direction would be greatly appreciated!

Thank You!

Summary Index Data:

<141>Apr 21 18:25:41 ff-sslvpn-1 tvv[3645]: 01490500:5: 7a35ba21: **New session** from client IP 255.255.255.0
<141>Apr 21 18:25:43 ff-sslvpn-1 axx[3594]: 01490116:5: 7a35ba21: User chose option: 0
<141>Apr 21 18:25:46 ff-sslvpn-1 axx[3594]: 01490116:5: 7a35ba21: User chose option: 1
<141>Apr 21 18:26:22 ff-sslvpn-1 axx[3594]: 01490010:5: 7a35ba21: Username 'Altavista'
<141>Apr 21 18:26:22 ff-sslvpn-1 axx[3594]: 01490115:5: 7z15ba21: Following rule 'G-S-
<141>Apr 21 18:26:26 ff-sslvpn-1 axx[3594]: 01490008:5: 7z15ba21: Connectivity resource '/LIVER
<141>Apr 21 18:26:26 ff-sslvpn-1 axx[3594]: 01490128:5: 7z15ba21: Webtop '/LIVER/WT_
<141>Apr 21 18:26:26 ff-sslvpn-1 axx[3594]: 01490115:5: 7z15ba21: Following rule 'fallback' 
<141>Apr 21 18:26:26 ff-sslvpn-1 axx[3594]: 01490115:5: 7z15ba21: Following rule 'Out' 
<141>Apr 21 18:26:26 ff-sslvpn-1 axx[3594]: 01490005:5: 7z15ba21: Following rule 'Out' from item '
<141>Apr 21 18:26:26 ff-sslvpn-1 axx[3594]: 01490102:5: 7z15ba21: Access policy result: Network_Access
<141>Apr 21 18:26:31 ff-sslvpn-1 tvv2[3645]: 01490549:5: 7z15ba21: Assigned PPP Dynamic I
<141>Apr 21 18:26:31 ff-sslvpn-1 tvv2[3645]: 01490505:5: 7z15ba21: PPP tunnel 0x5700f3c9a900 started.
<141>Apr 21 18:26:40 ff-sslvpn-1 tvv[3645]: 01490501:5: 7z15ba21: **Session deleted** due to user logout request.

summary_index=vendor-traffic-grp1
Session start = New Session
Session end = Session deleted
session_id = 7z15ba21
Table/panel/dashboard will be: username, session_id, duration
** i'll figure out how to visually show active vs closed connections

0 Karma
1 Solution

Esteemed Legend

First of all, you forgot the pipe ( | ) before the transaction command so that may be part of the problem; in any case, try this:

index=test1 | stats earliest(_time) AS earliest latest(_time) AS latest BY vendor_session_id | eval duration = tostring((latest-earliest), "duration")

View solution in original post

0 Karma

Contributor

It seems I may be approaching my task incorrectly... The general consensus is to use STATS and work without the summary index ... I appreciate your responses and I am going to close down this question.

My problems seem to stem from the combination of using a transaction command and an index summary.
If I continue this route, and find a solution.... i'll add comments to my previous questions.

Thank You.

0 Karma

Esteemed Legend

First of all, you forgot the pipe ( | ) before the transaction command so that may be part of the problem; in any case, try this:

index=test1 | stats earliest(_time) AS earliest latest(_time) AS latest BY vendor_session_id | eval duration = tostring((latest-earliest), "duration")

View solution in original post

0 Karma

Contributor

Concerning the pipe, it was a copy/paste error .. thank you.

0 Karma

Legend

Instead of the transaction command (slow) you could try this approach to get the desired results.

.... | rex "\*\*(?<state>.*)\*\*" | rex "Username\s'(?<user>\w+)'" | rex "(?<session>7\w+1)" | stats earliest(eval(if(state=="New session", _time, null()))) as start earliest(eval(if(state=="Session deleted", _time, null()))) as end first(user) as user by session | eval duration=tostring(start-end, "duration")
0 Karma