topic Re: VPN connection information report in All Apps and Add-ons

VPN connection information report

Zhanali — Sat, 10 Apr 2021 06:40:25 GMT

Hello everybody.
I need your help.

I do not know so well about all the functionality of the Splunk, but I was given the task of monitoring the VPN connection. I've been sitting on this task for a while now, and I'm almost done. But I cannot decide the last point.
As in the picture below, I was able to collect some information. As in the first picture, I was able to subtract the time from the start to the end of each session. But if you start the search as "for the whole day", then there is no information about sessions that have not yet closed and that start at 23:00 and end at 2:00 for example.
Search string:

sourcetype=cisco:asa message_id=722051 OR message_id=113019 OR message_id=722011 user=zhanali
| eval session_info = if((message_id = "113019" OR message_id = "722011"), "session_end", "session_start")
| transaction startswith=eval(session_info="session_start") endswith=eval(session_info="session_end")
| eval end_time = _time + duration
| eval duration1 = tostring(duration, "duration")
| stats count as "all session number" list(_time) as "start of sessions" list(end_time) as "end of sessions" list(duration1) as "durations per session" sum(duration) as secund by Username
| eval "full time for tuday" = tostring(secund, "duration")
| fields - secund
| convert ctime("start of sessions")
| convert ctime("end of sessions")

Result:

Here 113019 means disconnect, 722011 reconnect and 722051 session assigned. This means after reconnect again come syslog as session assigned.

How can i get and display the previously mentioned time intervals as start - 23:00 and end 00:00, start - 00:00 and end - 2:00? And more about active sessions like session active?

Below is the information about the first and last activity:

Please, HELP ME!

Re: VPN connection information report

ITWhisperer — Sat, 10 Apr 2021 07:03:30 GMT

Your transactions are complete i.e. they start with your start string and end with your end string. If you want incomplete transactions then you probably need to keep orphans and deal with the consequences of missing data for your duration calculations.

Re: VPN connection information report

Zhanali — Sat, 10 Apr 2021 07:29:08 GMT

I understood it. What option can you suggest without using the transaction command?
Thank you!

Re: VPN connection information report

ITWhisperer — Sat, 10 Apr 2021 08:46:42 GMT

Can you share some (anonymised) events covering start of day (without start of "transaction"), during day (with start and end of "transaction") and end of day (without end of transaction)?

Re: VPN connection information report

Zhanali — Sat, 10 Apr 2021 09:34:51 GMT

Of course.

Last event. I'm still on vpn:

And here I specifically reconnected manually:

And here is the first event for today:

Re: VPN connection information report

ITWhisperer — Sat, 10 Apr 2021 10:05:02 GMT

Sorry I should have been clearer, can you share them in a code </> box so I can paste them into a makeresults search?

Re: VPN connection information report

Zhanali — Sat, 10 Apr 2021 12:27:46 GMT

Oh. Here, three different messages

Apr 9 00:43:54 192.168.222.10 Apr 08 2021 18:43:54: %ASA-4-113019: Group = ContractorsCertProfile, Username = zhanali, IP = 89.106.237.226, Session disconnected. Session Type: SSL, Duration: 4h:16m:49s, Bytes xmt: 15665160, Bytes rcv: 3472558, Reason: User Requested Apr 10 07:19:37 192.168.222.10 Apr 09 2021 11:19:37: %ASA-5-722011: Group <ContractorsCertPolicy> User <zhanali> IP <89.106.237.226> SVC Message: 17/WARNING: Reconnecting the VPN tunnel.. Apr 9 09:32:08 192.168.222.10 Apr 09 2021 03:32:08: %ASA-4-722051: Group <ContractorsCertPolicy> User <zhanali> IP <89.106.237.226> IPv4 Address <192.168.110.13> IPv6 address <::> assigned to session

Re: VPN connection information report

ITWhisperer — Mon, 12 Apr 2021 10:58:50 GMT

OK I made some results based on your examples

The process is to sort by time, then use streamstats to get the previous start time for each user

Where there isn't a start time, snap to the start of the endtime day

Then reverse the time sort and use streamstats to get the previous end time for each user

Where there isn't an end time, snap to the end of the starttime day

Calculate the duration

Where there are start and end events they will now be doubled up so you can collect the duration (also doubled) by user and start and end times to provide events for each session

| makeresults | eval _raw="Apr 9 01:32:08 192.168.222.10 Apr 09 2021 01:32:08: %ASA-4-113019: Group = ContractorsCertProfile, Username = zhanali, IP = 89.106.237.226, Session disconnected. Session Type: SSL, Duration: 4h:16m:49s, Bytes xmt: 15665160, Bytes rcv: 3472558, Reason: User Requested Apr 9 01:42:08 192.168.222.10 Apr 09 2021 01:42:08: %ASA-4-113019: Group = ContractorsCertProfile, Username = ilanahz, IP = 89.106.237.226, Session disconnected. Session Type: SSL, Duration: 4h:16m:49s, Bytes xmt: 15665160, Bytes rcv: 3472558, Reason: User Requested Apr 9 09:32:08 192.168.222.10 Apr 09 2021 03:32:08: %ASA-4-722051: Group <ContractorsCertPolicy> User <zhanali> IP <89.106.237.226> IPv4 Address <192.168.110.13> IPv6 address <::> assigned to session Apr 9 09:42:08 192.168.222.10 Apr 09 2021 03:42:08: %ASA-4-722051: Group <ContractorsCertPolicy> User <ilanahz> IP <89.106.237.226> IPv4 Address <192.168.110.13> IPv6 address <::> assigned to session Apr 9 11:19:37 192.168.222.10 Apr 09 2021 11:19:37: %ASA-5-722011: Group <ContractorsCertPolicy> User <zhanali> IP <89.106.237.226> SVC Message: 17/WARNING: Reconnecting the VPN tunnel.. Apr 9 11:29:37 192.168.222.10 Apr 09 2021 11:29:37: %ASA-5-722011: Group <ContractorsCertPolicy> User <ilanahz> IP <89.106.237.226> SVC Message: 17/WARNING: Reconnecting the VPN tunnel.. Apr 9 11:19:38 192.168.222.10 Apr 09 2021 11:19:38: %ASA-4-722051: Group <ContractorsCertPolicy> User <zhanali> IP <89.106.237.226> IPv4 Address <192.168.110.13> IPv6 address <::> assigned to session Apr 9 11:29:38 192.168.222.10 Apr 09 2021 11:29:38: %ASA-4-722051: Group <ContractorsCertPolicy> User <ilanahz> IP <89.106.237.226> IPv4 Address <192.168.110.13> IPv6 address <::> assigned to session Apr 9 18:43:54 192.168.222.10 Apr 09 2021 18:43:54: %ASA-4-113019: Group = ContractorsCertProfile, Username = zhanali, IP = 89.106.237.226, Session disconnected. Session Type: SSL, Duration: 4h:16m:49s, Bytes xmt: 15665160, Bytes rcv: 3472558, Reason: User Requested Apr 9 18:53:54 192.168.222.10 Apr 09 2021 18:53:54: %ASA-4-113019: Group = ContractorsCertProfile, Username = ilanahz, IP = 89.106.237.226, Session disconnected. Session Type: SSL, Duration: 4h:16m:49s, Bytes xmt: 15665160, Bytes rcv: 3472558, Reason: User Requested Apr 9 23:32:08 192.168.222.10 Apr 09 2021 23:32:08: %ASA-4-722051: Group <ContractorsCertPolicy> User <zhanali> IP <89.106.237.226> IPv4 Address <192.168.110.13> IPv6 address <::> assigned to session Apr 9 23:42:08 192.168.222.10 Apr 09 2021 23:42:08: %ASA-4-722051: Group <ContractorsCertPolicy> User <ilanahz> IP <89.106.237.226> IPv4 Address <192.168.110.13> IPv6 address <::> assigned to session" | multikv noheader=t | fields _raw | rex "([\S]+\s){3}(?<_time>([\S]+\s){4})([^\-]+\-){2}(?<messageid>\d+)" | eval _time=strptime(_time,"%b %d %Y %H:%M:%S:") | rex "Username = (?<user>[\w]+)" | rex "User \<(?<user>[^\>]+)" | eval starttime=if(messageid=722051,_time,null) | eval endtime=if(messageid=113019 OR messageid=722011,_time,null) | sort _time | streamstats last(starttime) as starttime by user | eval starttime=coalesce(starttime,relative_time(endtime,"@d")) | sort - _time | streamstats last(endtime) as endtime by user | eval endtime=coalesce(endtime,relative_time(starttime,"+1d@d")) | fieldformat starttime=strftime(starttime,"%Y-%m-%d %H:%M:%S") | fieldformat endtime=strftime(endtime,"%Y-%m-%d %H:%M:%S") | sort _time | eval duration=endtime-starttime | fieldformat duration=tostring(duration,"duration") | stats values(duration) as duration by user starttime endtime

Re: VPN connection information report

Zhanali — Tue, 13 Apr 2021 09:23:10 GMT

Hello!

Thanks for answer .

I have modernized your search code and got the following result:

sourcetype=cisco:asa message_id=722051 OR message_id=113019 OR message_id=722011 zhanali | multikv noheader=t | eval starttime=if(message_id=722051,_time,null) | eval endtime=if(message_id=113019 OR message_id=722011,_time,null) | sort _time | streamstats last(starttime) as starttime by user | eval starttime1=coalesce(starttime,relative_time(endtime,"@d")) | sort - _time | streamstats last(endtime) as endtime by user | eval endtime1=coalesce(endtime,relative_time(starttime,"+1d@d")) | eval starttime=strftime(starttime1,"%Y-%m-%d %H:%M:%S") | eval endtime=strftime(endtime1,"%Y-%m-%d %H:%M:%S") | sort _time | eval duration=endtime1-starttime1 | eval duration=tostring(duration,"duration") | stats values(duration) as duration by user starttime endtime

Result when the first disconnect event suits. Is it possible, if the session is active, to change the endtime for the start time of the search?

Re: VPN connection information report

Zhanali — Tue, 13 Apr 2021 09:25:50 GMT

By the way, I also added a list command.

Re: VPN connection information report

ITWhisperer — Tue, 13 Apr 2021 09:37:33 GMT

Yes, you need to change it to:

| addinfo | eval starttime1=coalesce(starttime,info_min_time)

You could do a similar thing with info_max_time for the endTime

P.S. This is probably not needed as it was part of the set up of the example data:

| multikv noheader=t

Re: VPN connection information report

Zhanali — Fri, 16 Apr 2021 08:44:23 GMT

Hello. Thank you for help.

It turns out that we still have a lot of different information about VPN connection in our logs, with your method it is not so good to achieve a good result. For this on this topic, I will create another request.