How to search currently active VPN sessions?

[mattbirk]

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?

[dwaddle]

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]

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

[dwaddle]

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.

[mattbirk]

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]

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)