Splunk Search

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

sanorthrup
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
1 Solution

kristian_kolb
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

kristian_kolb
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

sanorthrup
Path Finder

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

0 Karma

kristian_kolb
Ultra Champion

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

0 Karma

sanorthrup
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 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]

0 Karma

kristian_kolb
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

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

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

0 Karma

sanorthrup
Path Finder

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

0 Karma

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

0 Karma

sanorthrup
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

bmacias84
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...