All Apps and Add-ons

VPN connection information report

Zhanali
Path Finder

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:

Zhanali_1-1618035106669.png

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:

Zhanali_2-1618036221133.png

Please, HELP ME!

 

 

Labels (1)
0 Karma
1 Solution

Zhanali
Path Finder

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

Zhanali_0-1618304297706.png

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?

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

Zhanali
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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)?

0 Karma

Zhanali
Path Finder

Of course.

Last event. I'm still on vpn:

Zhanali_2-1618047027672.png

And here I specifically reconnected manually:

Zhanali_1-1618046887974.png

And here is the first event for today:

Zhanali_3-1618047262533.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

Zhanali
Path Finder

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

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

Zhanali
Path Finder

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

Zhanali_0-1618304297706.png

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?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

0 Karma

Zhanali
Path Finder

By the way, I also added a list command.

Zhanali_0-1618305889748.png

 

0 Karma

Zhanali
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...