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!

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW!Every day the list of sources Admins are responsible for gets bigger and bigger, often making the ...

Remediate Threats Faster and Simplify Investigations With Splunk Enterprise Security ...

REGISTER NOW!Join us for a Tech Talk around our latest release of Splunk Enterprise Security 7.2! We’ll walk ...

Introduction to Splunk AI

WATCH NOWHow are you using AI in Splunk? Whether you see AI as a threat or opportunity, AI is here to stay. ...