Splunk Search

How to search currently active VPN sessions?

mattbirk
Explorer

So my data has, for example, code 001 for connected and 002 for disconnected. Also, each VPN session has a unique session ID, so they can be related to each other. Each VPN session will eventually have a VPN Connected: 001 and VPN disconnected: 002, but if they have yet to disconnect than there will only be 001.

When I search for 001 it will show both connections that are currently open, and ones that have closed.

I am trying to limit this to only search those session IDs that have not closed. I assume this needs to be done joining based on Session ID? What would be the best way to achieve this?

Tags (4)

dwaddle
SplunkTrust
SplunkTrust

I'll comment that one really good way of doing this is just use RADIUS accounting and let RADIUS write accounting data into a MySQL DB. Then, use DB-Connect to query said DB, and "off you pop!"

But otherwise, let's attack this using techniques show by Alex in http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/

Let's begin by making a lookup table that has four columns -- sessionid, start_time, end_time, and username. We'll populate said lookup table once by doing this search over all time:

sourcetype=vpn vpn_session=* (code=001 OR code=002) 
| stats max(eval(if(code=="001",_time,0))) as start_time, 
    max(eval(if(code=="002",_time,0))) as end_time, 
    first(username) as username by vpn_session
| where start_time > 0 AND end_time > 0 AND end_time < ( now() - 86400 * 90 ) 
| outputlookup vpn_sessionstate.csv

Now we have a CSV file that has every VPN session ever, unless it successfully ended more than 90 days ago. From here we can apply the "incremental update phase", by running in a scheduled search:

sourcetype=vpn vpn_session=* (code=001 OR code=002) 
| stats max(eval(if(code=="001",_time,0))) as start_time, 
    max(eval(if(code=="002",_time,0))) as end_time, 
    first(username) as username by vpn_session
| inputlookup append=true vpn_sessionstate.csv
| stats max(start_time) as start_time, max(end_time) as end_time, first(username) as username by vpn_session
| where start_time > 0 AND end_time > 0 AND end_time < ( now() - 86400 * 90 ) 
| outputlookup vpn_sessionstate.csv

Hopefully, with this incremental update, our CSV file should constantly have VPN sessions that have never closed, or closed in the last 90 days. Now, you can easily run a search like:

| inputlookup vpn_sessionstate.csv | where end_time==0

nazanin2016
Path Finder

@dwaddle:what is the TTl for CSV file that has every VPN session , when we save a lookup table does it refresh based on real time logs?how often does it refresh?

0 Karma

dwaddle
SplunkTrust
SplunkTrust

I don't understand the question unfortunately. When you build a lookup file to maintain state, the amount of time things stay in that lookup file are totally up to how you build your searches that maintain the lookup file. Also, this is not a real time search, but a scheduled one - you can schedule it to run very frequently, but it is not real time.

0 Karma

mattbirk
Explorer

So the way I figured it out was using the transaction command to link together by session ID, than was able to get it from there. Thanks!

acharlieh
Influencer

assuming your events have fields of vpn_session and code, and a sourcetype vpn:

You could have a search like:
sourcetype=vpn vpn_session=* code=001 OR code=002 | stats latest(code) as code by vpn_session | where code="001"

(It's a way, it may or may not be the best way)

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 ...