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(?
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?
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
that's very close it's just not converting the time to seconds so the TotalDuration field is empty.
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
this worked for me, above solution helped me finish this off.
host=10.45.16.40 vpn/ "Session disconnected" | rex field=_raw "Duration:(?
that is almost working, its just not converting the time into seconds so the TotalDuration field is empty.
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