Archive

I am trying to show VPN connection times with the time of day

justinearly
New Member

I am trying to show how long someone has been connected to the VPN for the last X days. There is an action field with the results of "connected" or "closed". How do I show the times in between as connected time then send the connected time with the start times and days to a visualization?

index=vpn NOT user=System user=Billy juniper_sslvpn_action!=NULL juniper_sslvpn_action!=succeeded

Tags (1)
0 Karma

woodcock
Esteemed Legend

Like this:

index=vpn NOT user=System sslvpn_action!=NULL juniper_sslvpn_action!=succeeded
| bucket _time span=1d
| streamstats count(eval(juniper_sslvpn_action="closed")) AS sessionID BY _time user
| stats range(_time) AS duration BY  sessionID user
| stats sum(duration) AS total_connected_time BY user _time
| eval total_connected_time = tostring(total_connected_time, "duration")
| fieldformat _time = strftime(_time, "%b %d")
| field - sessionID
0 Karma

justinearly
New Member

By "times" I mean length of time connected to VPN each day.

May 20 -- JJones -- 7 hours 6 min
May 19 -- JJones -- 6 hours 54 min

0 Karma

woodcock
Esteemed Legend

Try my updatef answer.

0 Karma

justinearly
New Member

There was an error in the last two lines....
Unknown search command 'field'.
Error in 'fieldformat' command: The expression is malformed. Expected ).

0 Karma

woodcock
Esteemed Legend

I had a typo in the strftime but I fixed it; try again.

0 Karma

justinearly
New Member

This is closer to what I am looking for. How do I show connection times by day?

Basically a search I where I can input a userID and it will show how long they were connected to the VPN by the day.

0 Karma

woodcock
Esteemed Legend

By "times" do you mean "count" or "list of in/out pairs"?

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi,
After your search you have to correlate events using e.g. TransactionId or user and Ip or identifying start and end transaction strings.
In this way, you'll have an additional field called "duration" that you can sum.
Something like this:
Your_search
| transaction user IP startswith="start_string" endswith="end_string"
| stats sum(duration) AS total by user
Bye.
Giuseppe

0 Karma