Splunk Search

how to get the total sum based on specific field

Explorer

I have a query which runs over a month period which lists all users connected via VPN and the duration of each connection.
What I would like to do is list the amount of time each user is connected.
I have the query:

host=10.45.16.40 vpn/ "Session disconnected" | rex field=_raw "Duration(?[^,]*)" | stats count by Username duration which returns results:

Username                         duration          count

1 vpn/anna.ostren@ingostlm.e : 0h:27m:11s 1

2 vpn/anna.ostren@ingostlm.e : 1h:21m:17s 1

3 vpn/anna.ostren@ingostlm.e : 3h:06m:18s 1

4 vpn/annika.mll@ingostlm.e : 0h:50m:25s 1

5 vpn/annika.mll@ingostlm.e : 1h:44m:12s 1

6 vpn/at@unegy.k : 0h:11m:16s 1

so I would like to combine all users of the same name and list the total time they were connected.

Is this possible?

1 Solution

SplunkTrust
SplunkTrust

Try this

host=10.45.16.40 vpn/ "Session disconnected" | rex field=_raw "Duration(?<duration>[^,]*)" | stats count by Username duration | rex field=duration "(?<Hour>.*)h:(?<Minute>.*)m:(?<Second>.*)s" | eval duration=Hour*3600 + Minute*60 + Second | stats sum(duration) as TotalDuration, sum(count) as SessionCount by Username

View solution in original post

Explorer

that's very close it's just not converting the time to seconds so the TotalDuration field is empty.

0 Karma

SplunkTrust
SplunkTrust

Try this

host=10.45.16.40 vpn/ "Session disconnected" | rex field=_raw "Duration(?<duration>[^,]*)" | stats count by Username duration | rex field=duration "(?<Hour>.*)h:(?<Minute>.*)m:(?<Second>.*)s" | eval duration=Hour*3600 + Minute*60 + Second | stats sum(duration) as TotalDuration, sum(count) as SessionCount by Username

View solution in original post

Explorer

this worked for me, above solution helped me finish this off.
host=10.45.16.40 vpn/ "Session disconnected" | rex field=_raw "Duration:(?[^,])" | stats count by Username duration | rex field=duration "(?(\d+))h:(?(\d+))m:(?(\d+))s" | eval duration=Hour3600 + Minute*60 + Second | stats sum(duration) as TotalDuration, sum(count) as SessionCount by Username

0 Karma

Explorer

that is almost working, its just not converting the time into seconds so the TotalDuration field is empty.

0 Karma

Splunk Employee
Splunk Employee

Once you convert the duration field to a number (of seconds?), you can easily calculate the total duration with something like stats sum(duration) AS total_time by Username

0 Karma