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
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
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
My unique ID was not actually unique. That's been fixed and this works great now. Thanks very much for your help.
yes - eval on inner and outer. what was the result?
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 Extract_NAPVPN | search Acct_Status_Type="1" | eval uniqueID = ComputerName+Acct_Session_Id | search NOT [search sourcetype=NAPVPN Acct_Status_Type="2" | eval uniqueID = ComputerName+Acct_Session_Id | fields + uniqueID]
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
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.
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 Extract_NAPVPN | search Acct_Status_Type ="1" OR Acct_Status_Type ="2" | transaction ComputerName Acct_Session_Id maxspan=48h maxevents=2 keepevicted=true startswith="Acct_Status_Type ="1"" endswith="Acct_Status_Type ="2"" | where eventcount=1
Yes, exactly. Acct_Status_Type 1 = user login, Acct_Status_Type 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 Acct_Session_Id, but since we have multiple server there are dupe Acct_Session_Id's, so I'm trying to join ComputerName + Acct_Session_Id to make a unique session ID
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" Acct_Status_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.
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.
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?