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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...