topic Re: how to get the total sum based on specific field in Splunk Search

how to get the total sum based on specific field

ikcresswell — Thu, 29 May 2014 13:03:04 GMT

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?

Re: how to get the total sum based on specific field

sowings — Thu, 29 May 2014 13:27:34 GMT

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

Re: how to get the total sum based on specific field

somesoni2 — Thu, 29 May 2014 13:29:56 GMT

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

Re: how to get the total sum based on specific field

ikcresswell — Thu, 29 May 2014 14:06:17 GMT

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

Re: how to get the total sum based on specific field

ikcresswell — Thu, 29 May 2014 14:07:15 GMT

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

Re: how to get the total sum based on specific field

ikcresswell — Mon, 28 Sep 2020 16:45:11 GMT

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=Hour*3600 + Minute*60 + Second | stats sum(duration) as TotalDuration, sum(count) as SessionCount by Username