topic Re: Getting a value from subsearch in Splunk Search

Getting a value from subsearch

tmontney — Fri, 31 Mar 2017 19:26:37 GMT

(index="myindex" OR index="wineventlog") AND ((host=MYSERVER1 OR host=MYSERVER2) AND (EventCode=20274 OR EventCode=20271)) OR ((fw="192.168.10.20") AND (msg="User logged in" OR msg="User failed to logon"))
| rename _time AS earliest
| rename EventCode AS tEventCode
| eval Username=case(tEventCode=20274, mvindex(split(body, " "), 5), tEventCode=20271, mvindex(split(body, " "), 3), 1=1, usr)
| eval preSource=case(tEventCode=20271, mvindex(split(body, " "), 6), tEventCode=20274, mvindex(split(body, " "), 14), 1=1, src)
| eval Source=[search index="wineventlog" EventCode=6278 Connection_Request_Policy_Name="MYPOLICY" | eval SubSource=case(EventCode=="20274" AND Account_Name==Username AND earliest==_time, Calling_Station_Identifier) | eval SubSource=case(SubSource="", Source) | fields SubSource | rename SubSource as query]
| eval FSource=case(like(Source, "10.%"), Source, like(Source,"172.16.%"), Source, like(Source,"192.168.%"), Source, 1=1, "http://". Source .".ipaddress.com")
| sort Date Time Reason
| table Username Reason FSource

Get the following error.

Error in 'eval' command: The expression is malformed. An unexpected character is reached at ')'.

Basically, if the main search gets a result that's EventCode 20274, it's to perform another search looking for an event 6278 with the same account name and date/time as the 20274 event. I'm looking to extract the IP address in 6278, as it isn't contained in 20274.

This 'eval' is called near the end of my query, before I sort and table everything.

Event 20274 is when a user successfully authenticates with a RADIUS server. It contains the username and private IP address of the session. The private IP address is the IP assigned to the user, from a DHCP pool. The problem is just that, it's a private IP. If the user failed to authenticate, then it gives the public IP. If you want to know the public IP of a successful RADIUS authentication, you need to look at event 6278 (NAP policy). On successful login, the user requests a NAP policy, one being RADIUS. The NAP event lists the public IP that requested it.

tl;dr I want to correlate 20274's with 6278's, as both have information I need. Where 20274 exists, 6278 exists.

The other issue is that there really isn't that much between the two. There's no direct reference. However, since they both happen at the same time, I'd use time and username assigned to match them.

Re: Getting a value from subsearch

somesoni2 — Fri, 31 Mar 2017 19:43:05 GMT

You current subsearch returns value with field name and hence the error. You need to return the value, so try like this

| eval Source=[search index="wineventlog" EventCode=6278 Connection_Request_Policy_Name="POLICY NAME" | eval SubSource=case(EventCode=="20274" AND Account_Name==Username AND earliest==_time, Calling_Station_Identifier) | eval SubSource=case(SubSource="", Source) | fields SubSource| rename SubSource as query]

 | eval Source=[search index="wineventlog" EventCode=6278 Connection_Request_Policy_Name="POLICY NAME" | eval SubSource=case(EventCode=="20274" AND Account_Name==Username AND earliest==_time, Calling_Station_Identifier) | eval SubSource=case(SubSource="", Source) | eval query="\"".SubSource."\"" | table query]

Re: Getting a value from subsearch

tmontney — Fri, 31 Mar 2017 19:56:04 GMT

No good. I updated my post with the whole query.

Re: Getting a value from subsearch

somesoni2 — Fri, 31 Mar 2017 20:08:20 GMT

How many results the subsearch is returning? It should be returning just one, right?
Try v2 as well.

Re: Getting a value from subsearch

tmontney — Fri, 31 Mar 2017 20:15:15 GMT

Yeah, there should be 1 to 1. Basically, if the event id in the main search is 20274, I search event code 6278 (as subsearch) to replace the IP address found (in 20274). The IP in 20274 is always private, and the 6278 event has the public ip.

Re: Getting a value from subsearch

tmontney — Fri, 31 Mar 2017 20:17:26 GMT

Same result.

Re: Getting a value from subsearch

somesoni2 — Fri, 31 Mar 2017 20:49:01 GMT

You can't pass a value from main search to subsearch. Your subsearch is getting data for EventCode=6278 and your eval-case is base off EventCode on main search, thus it returns null and hence the error. How can you correlate which private IP belongs to which public IP, username and time??

Re: Getting a value from subsearch

DalJeanis — Fri, 31 Mar 2017 20:50:22 GMT

Semantic note - Your initial search is

(index) AND (condition) OR (condition)

Combining ANDs and ORs at the same level is problematic. AND has the higher precedence, so that is semantically interpreted as

( (index) AND  (condition))  OR  (condition)

Indexes are special, so Splunk MIGHT override the above precedence. Without the word AND, I would expect the index= clause to apply to both the following alternatives, but with the AND, I would not be certain.

You should clarify the search with parenthesis. I believe you meant..,.

(index) AND ( (condition) OR (condition) )

Re: Getting a value from subsearch

tmontney — Fri, 31 Mar 2017 21:12:11 GMT

It's not ideal, and the two events don't have much more than time, account, and private ip. Both events should happen at the same time. If I compare those three things, there's very little chance it's another event.

OK, then how would you approach it?

Re: Getting a value from subsearch

somesoni2 — Fri, 31 Mar 2017 21:26:16 GMT

The public IP that you're looking for is only for few specific hosts OR they can be many more dynamically? Are you looking for public IP for host=SERVER1 OR host=SERVER2 ?

Re: Getting a value from subsearch

tmontney — Fri, 31 Mar 2017 21:30:52 GMT

I updated the OP with more detail about what I'm trying to get.

Re: Getting a value from subsearch

somesoni2 — Fri, 31 Mar 2017 21:46:36 GMT

I guess you would need join here (due to already multiple data sources in base search, can't think of an easy way to merge there). Give this a try

(index="myindex" OR index="wineventlog") ((host=SERVER1 OR host=SERVER2) AND (EventCode=20274 OR EventCode=20271)) 
OR ((fw="192.168.1.33") AND (msg="User login successful" OR msg="User login failed - invalid password")) 
OR ((host=SERVER1 OR host=SERVER2) EventCode=6278 Connection_Request_Policy_Name="PBDC VPN Connections")
 | eval joinEventCode=if(EventCode=20274,6278,null)
 | eval Username=case(EventCode=20274, mvindex(split(body, " "), 5), EventCode=20271, mvindex(split(body, " "), 3), 1=1, usr)
 | eval preSource=case(EventCode=20271, mvindex(split(body, " "), 6), EventCode=20274, mvindex(split(body, " "), 14), 1=1, src)
 | join _time index Username joinEventCode [search index="wineventlog" EventCode=6278 Connection_Request_Policy_Name="PBDC VPN Connections" | fields _time index Account_Name EventCode Calling_Station_Identifier| rename Account_Name as Username EventCode as joinEventCode ]
 | eval Source=case(isnull(Calling_Station_Identifier) OR Calling_Station_Identifier="", Source)
 | eval FSource=case(like(Source, "10.%"), Source, like(Source,"172.16.%"), Source, like(Source,"192.168.%"), Source, 1=1, "http://". Source .".ipaddress.com")
 | sort Date Time Reason
 | table Username Reason FSource

Re: Getting a value from subsearch

woodcock — Fri, 31 Mar 2017 22:06:48 GMT

Use this line instead:

 | eval Source=[search index="wineventlog" EventCode=6278 Connection_Request_Policy_Name="PBDC VPN Connections" | eval SubSource=if((EventCode=="20274" AND Account_Name==Username AND earliest==_time), Calling_Station_Identifier, null()) | eval SubSource=coalesce(SubSource, Source) | return $SubSource]

Re: Getting a value from subsearch

DalJeanis — Fri, 31 Mar 2017 22:26:12 GMT

If I've interpreted your code and comment correctly, this should get the _time of a connection, the Username, and the private IP address.

((index="myindex" OR index="wineventlog") AND (host=SERVER1 OR host=SERVER2) AND EventCode=20274)
| eval Username=coalesce(mvindex(split(body, " "), 5),usr)
| eval preSource=coalesce(mvindex(split(body, " "), 14),src)
| table _time Username preSource
| rename preSource as privateIP

And this should get the _time of authentication, the Username, and the public IP address

(index="wineventlog" EventCode=6278 Connection_Request_Policy_Name="PBDC VPN Connections")
| table _time, Account_Name, Calling_Station_Identifier
| rename Account_Name as Username, Calling_Station_Identifier as publicIP

...so try this...

 earliest=-10m latest=-5m EventCode=20274
(index="myindex" OR index="wineventlog") 
(host=SERVER1 OR host=SERVER2) 
| eval Username=coalesce(mvindex(split(body, " "), 5),usr)
| eval preSource=coalesce(mvindex(split(body, " "), 14),src)
| table _time Username preSource
| rename preSource as privateIP
| bin _time as Time span=1m
| head 10 
| join type=left Time Username 
   [ (earliest=-10m latest=-5m index="wineventlog" EventCode=6278 Connection_Request_Policy_Name="PBDC VPN Connections")
    | table _time, Account_Name, Calling_Station_Identifier
    | rename Account_Name as Username, Calling_Station_Identifier as publicIP
    | bin _time as Time span=1m
     | rename _time as authTime
   ]
| table Time _time authTime Username privateIP publicIP

... if that sample works, then we can proceed to pull in your other records.

Re: Getting a value from subsearch

DalJeanis — Fri, 31 Mar 2017 22:33:31 GMT

Needs more of an overhaul, probably to a map/search command. The OP is trying to differentiate in the subsearch between events in the search itself.

I don't think that EventCode code has any chance of working, since the parent event has EventCode=20274 or EventCode=20271 and the child event has EventCode=6278.

Likewise, earliest in that spot isn't going to have the desired effect, if it works at all.

Re: Getting a value from subsearch

DalJeanis — Tue, 29 Sep 2020 13:30:28 GMT

Looks plausible. Needs comma in rename command, IIRC.

Not sure why index is in the join...?

My preference would be to change 1=1 to true() , and to align the case statements to the same order.

Also, Source needs to get Calling_Station_Identifier when it is present.

I don't see where Date, Time, or Reason are coming from, or why the table command would remove the Date and Time if that's how they are going to sort.

Re: Getting a value from subsearch

DalJeanis — Fri, 31 Mar 2017 22:43:22 GMT

Never mind, somesoni2's more recent answer is farther along than this.

Re: Getting a value from subsearch

woodcock — Fri, 31 Mar 2017 22:44:31 GMT

I was focusing on the error that he had, not on the entire solution. I am sure that you are correct about the other problems.

Re: Getting a value from subsearch

DalJeanis — Fri, 31 Mar 2017 22:47:24 GMT

I kinda figured it that way. The more I looked at it, the more it seemed the thing was like Hughes' Spruce Goose ( never going to fly).

Somesoni2 overhauled the whole thing, and it appears fairly close but for some nits.

Re: Getting a value from subsearch

tmontney — Mon, 03 Apr 2017 13:04:09 GMT

Must have left those by mistake. The eval references are normally there.