Splunk Search
Highlighted

Need help with an extract, combining fields, then finding users which have logged in but not logged out

Path Finder

I need to find the log entries for users which have VPN'd in but not yet logged out. Each VPN session is supposed to have a unique "Acct_Session_Id" but unfortunately we have duplicate Acct_Session_Id's because the logs come from multiple servers. So the Acct_Session_Id's are only unique to each ComputerName. My solution was to use an eval to combine ComputerName+Acct_Session_Id to create a new field with a truly unique ID. I've been calling this field UniqueID.

I created a transform which lets me extract the fields I care about Acct_Session_ID, Acct_Status_Type and ComputerName. I asked a similar question before for Cisco Secure ACS logs and got some great help and an answer. Now I'm trying to get the same data from IAS logs and the complexity of adding in the eval and the extract has got my head spinning in circles. Since it's got a subsearch, I'm not sure if I need to do the eval and extract for both.. Here's an example of what we use for Cisco Secure ACS:

sourcetype="ssl_vpn" Acct_Status_Type = Start NOT [search sourcetype="ssl_vpn" Acct_Status_Type = Stop | fields + Acct_Session_Id | format maxresults=1000000]

Here's my attempt at getting the same data with an extract and an eval:

sourcetype="NAPVPN" | extract Extract_NAPVPN | search Acct_Status_Type ="1" NOT [search sourcetype="NAPVPN" | extract Extract_NAPVPN | search Acct_Status_Type ="2" | eval UniqueID = ComputerName + Acct_Session_Id | fields + UniqueId | format maxresults=1000000]

The results I'm getting are exactly the same as I get when I do it without the subsearch

sourcetype="NAPVPN" | extract Extract_NAPVPN | search Acct_Status_Type ="1"

Please help

0 Karma
Highlighted

Re: Need help with an extract, combining fields, then finding users which have logged in but not logged out

Champion

Ok,I think I might be able to help. So are you trying to pass your sub search back and use it as part of your base search to filter events?

0 Karma
Highlighted

Re: Need help with an extract, combining fields, then finding users which have logged in but not logged out

Champion

Well I think I understand. To return a subsearch and use the result in your base search to filter you want to use the return command. The return command is used to pass values up from a subsearch and remove the need for format or head. Take a look at my pervious post subsearch-in-search-command-not-returning-results.


sourcetype="ssl_vpn" AcctStatus_Type = Start NOT [search sourcetype="ssl\vpn" Acct_Status_Type = Stop | fields + Acct_Session_Id |return Acct_Session_Id]


sourcetype="NAPVPN" | extract Extract_NAPVPN | search Acct_Status_Type ="1" NOT [search sourcetype="NAPVPN" | extract Extract_NAPVPN | search Acct_Status_Type ="2" | eval UniqueID = ComputerName + Acct_Session_Id | fields + UniqueId | return UniqueId]

You may have to play with limit.conf to increase the number of return values. By default subsearches only return 100 and can not exceed 10500. You might get around this by makeing the returned values into a single multvalued field.
Hope this help or gets you started. Dont forget to vote and accept answers that help.

0 Karma
Highlighted

Re: Need help with an extract, combining fields, then finding users which have logged in but not logged out

Path Finder

I tried this, but it did not help. When I tried it on the search which is working perfectly (sourcetype=ssl_vpn) using the return command actually made it not work.

0 Karma
Highlighted

Re: Need help with an extract, combining fields, then finding users which have logged in but not logged out

Path Finder

Yes, exactly. AcctStatusType 1 = user login, AcctStatusType 2 = user logout. So I want to see all of the logs of the user that have logged in, but do not have a corresponding logout. What's supposed to be unique to their session is the AcctSessionId, but since we have multiple server there are dupe AcctSessionId's, so I'm trying to join ComputerName + AcctSessionId to make a unique session ID

0 Karma
Highlighted

Re: Need help with an extract, combining fields, then finding users which have logged in but not logged out

Splunk Employee
Splunk Employee

I'd try using the "transaction" command.

Pseudocode (you may have to tweak, especially the quotes around the Acct_Status_Type="1" fields):

sourcetype="ssl_vpn" (Acct_Status_Type ="1" OR Acct_Status_Type ="2") | transaction ComputerName Acct_Session_Id maxspan=25h maxevents=2 keepevicted=true  startswith="Acct_Status_Type ="1"" endswith="Acct_Status_Type ="2" | where eventcount=1 

This should give you a list of connections that didn't exit, having only one event, the Acct_Status_Type="1" event. The "keepevicted=true" option keeps the non-matching Acct_Status_Type="1" events in the result set.

Highlighted

Re: Need help with an extract, combining fields, then finding users which have logged in but not logged out

Path Finder

Your logic is exactly right, but none of these fields exist until I load the extract. So I edited what you sent, but using this query I get more results than when I just search for logins. Any idea what I'm doing wrong?

sourcetype="NAPVPN" | extract ExtractNAPVPN | search AcctStatusType ="1" OR AcctStatusType ="2" | transaction ComputerName AcctSessionId maxspan=48h maxevents=2 keepevicted=true startswith="AcctStatusType ="1"" endswith="AcctStatus_Type ="2"" | where eventcount=1

0 Karma
Highlighted

Re: Need help with an extract, combining fields, then finding users which have logged in but not logged out

Ultra Champion

Basically your search says;

'From sourcetype NAPVPN, gimme all events that are marked as "start", but not those that have the following uniqueIDs'

The problem is that the uniqueID does not exist in the outer search, so the effect will be the same as skipping the subsearch altogether, as you noted.

You'd probably have more luck if you structure the search like so (simplified below);

sourcetype=xxx type=start | eval uniqueID=A+B | search NOT [sourcetype=xxx type=stop | eval uniqueID=A+B | fields + uniqueID]

/k

View solution in original post

Highlighted

Re: Need help with an extract, combining fields, then finding users which have logged in but not logged out

Ultra Champion

Oh, and you may want to investigate the possibility of creating a state table for VPN users, through the use of scheduled searches updating a lookup table.

http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/

/k

0 Karma
Highlighted

Re: Need help with an extract, combining fields, then finding users which have logged in but not logged out

Path Finder

This is a huge step in the right direction, but neither start (1) nor stop (2) are recognized fields without doing the extract. They're just a number after the 32nd comma. So when I put the extract back in, then it looks very similar to my initial query (but with the eval in teh outer search).

sourcetype=NAPVPN | extract ExtractNAPVPN | search AcctStatusType="1" | eval uniqueID = ComputerName+AcctSessionId | search NOT [search sourcetype=NAPVPN AcctStatusType="2" | eval uniqueID = ComputerName+AcctSession_Id | fields + uniqueID]

0 Karma