Getting a value from subsearch

[tmontney]

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

[tmontney · Accepted solution]

Ok, think I got it. The only issue I'm having is occasionally 6278 will come a second after a 20274. I thought maxspan would do it, but it doesn't seem like it is. I need _time to allow for a 2 second difference.

(index="myindex" OR index="wineventlog") AND ((host=MYSERVER01 OR host=MYSERVER02) AND (EventCode=20274) OR (Connection_Request_Policy_Name="MYPOLICY" AND EventCode=6278)) OR ((fw="192.168.10.20") AND (msg="User logged in" OR msg="User failed to logon"))
| rename Account_Name AS Username
| eval pEventCode=case(mvcount(EventCode)=2, mvindex(EventCode,1), 1=1,EventCode)
| eval Username=case(pEventCode=20274, mvindex(split(body, " "), 5), pEventCode=20271, mvindex(split(body, " "), 3), 1=1, usr)
| eval Reason=case(EventCode=20274, "User logged in", EventCode=20271, "User failed to logon", 1=1, msg)
| transaction _time maxspan=5s
| eval preUsername=case(mvcount(EventCode)=2, mvindex(Username, 1))
| eval Username=if(NOT isNull(preUsername), preUsername, Username)
| eval ppreSource=case(mvcount(EventCode)=2, Calling_Station_Identifier, fw="192.168.10.20", src, EventCode=20274, if(isNull(ppreSource),mvindex(split(body, " "), 14),""), EventCode=20271, if(isNull(preSource),mvindex(split(body, " "), 6),""), 1=1, null)
| eval SourceHost=case(like(ppreSource, "10.%"), ppreSource, like(ppreSource,"172.16.%"), ppreSource, like(ppreSource,"192.168.%"), ppreSource, 1=1, "http://". ppreSource .".ipaddress.com")
| eval Hostname=case(fw="192.168.10.20", "PBDC-MKE-SSL-VPN", 1=1, host)
| eval Date=strftime(_time, "%m-%d-%Y")
| eval Time=strftime(_time, "%H:%M:%S")
| sort Date Time Reason
| table Username Reason SourceHost Hostname Date Time

[DalJeanis]

Since you are using "time" to bind the transaction on, that makes it a bit tough.

You could try cloning the 6278 transaction a second earlier, and then killing any transaction that has only a lone 6278.

| bin _time as Time span=1s
| eval Time= if(EventCode=6278,mvappend(Time-1,Time),Time)
| mvexpand Time
| eval _time = Time
| transaction _time maxspan=5s
| where NOT (EventCode==6278 AND eventcount==1)

[woodcock]

There is a maxpause, too.

[DalJeanis]

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.

[DalJeanis]

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

[woodcock]

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]

[DalJeanis]

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.

[woodcock]

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.

[DalJeanis]

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.

[woodcock]

http://www.bbc.com/news/uk-wales-26025939
:>

[DalJeanis]

Heh. That's the wonderful thing about simulators... just like standards, there's so many of them to choose from...

[DalJeanis]

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

[somesoni2]

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]

v2

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

[tmontney]

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

[somesoni2]

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

[tmontney]

Same result.

[somesoni2]

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

[tmontney]

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?

[somesoni2]

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 ?

[tmontney]

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