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

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

sanorthrup — Thu, 09 May 2013 15:52:17 GMT

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

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

bmacias84 — Thu, 09 May 2013 17:41:48 GMT

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?

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

bmacias84 — Mon, 28 Sep 2020 13:52:23 GMT

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.

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

sanorthrup — Mon, 28 Sep 2020 13:52:25 GMT

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

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

sanorthrup — Fri, 10 May 2013 13:08:46 GMT

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.

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

Jon_Webster — Sat, 11 May 2013 23:38:16 GMT

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.

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

kristian_kolb — Sun, 12 May 2013 12:50:27 GMT

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]

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

kristian_kolb — Sun, 12 May 2013 13:08:33 GMT

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/

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

sanorthrup — Mon, 28 Sep 2020 13:53:20 GMT

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

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

sanorthrup — Mon, 28 Sep 2020 13:53:25 GMT

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

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

kristian_kolb — Mon, 13 May 2013 20:51:45 GMT

yes - eval on inner and outer. what was the result?

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

sanorthrup — Tue, 14 May 2013 16:58:34 GMT

My unique ID was not actually unique. That's been fixed and this works great now. Thanks very much for your help.