how to get the total sum based on specific field

[ikcresswell]

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?

[ikcresswell]

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

[somesoni2 · Accepted solution]

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

[ikcresswell]

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

[ikcresswell]

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

[sowings]

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