Splunk Search

how to get the total sum based on specific field

ikcresswell
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

somesoni2
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

ikcresswell
Explorer

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

0 Karma

somesoni2
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

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

0 Karma

ikcresswell
Explorer

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

0 Karma

sowings
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
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...