Exclude IP address returned from subsearch in main search

[apeadape]

Hello,

I've got a search query where I'm looking for unexpected ssh connections to my instances, but I've got one server where my IP address dynamically changes and I want to exclude the IP address of that host because I know there will be expected ssh connections from that IP address.

I'm running a sub search to look at aws description logs, grabbing the IP of the box based on it's name and returning the IP address in hopes I can use it in my main search. So far it's not working how I expect and I'm not sure why.

I would expect not to see entries for hostnameA with usernameA that's coming from a source IP that I'm getting from my subsearch, but my results include those entries.

Here's my search so far:

index=X sourcetype=linux_secure eventtype=sshd_authentication action=success
| eval exclude_host_ip=[ search index=X sourcetype=aws:description source=*:ec2_instances (tags.host=* OR tags.Name=*) earliest=-24h latest=now
| eval hostName=coalesce('tags.host', 'tags.Name')
| search hostName=dynamic_ip_hostname
| sort - _time
| dedup private_ip_address
| eval ip="\"".private_ip_address."\""
| return $ip]
| search
NOT (host=hostnameA AND user=usernameA AND user_src_ip=exclude_host_ip)
| table _time, user, host, user_src_ip
| sort - _time
| dedup _time user host user_src_ip
| rename _time as Time, user as "Username", host as "Host", user_src_ip as "Source IP"
| convert timeformat="%m-%d-%Y %H:%M:%S" ctime(Time)

[apeadape · Accepted solution]

I've been tinkering with this query off and on for a couple of day before I decided to post here and then of course, an hour after I post this I have an "ah ha" moment and I think this is working now.

Posting my fix here in case anybody has this question in the future. Please let me know if you know of a better or more efficient way to do this 🙂

Instead of doing an eval and then trying to use that value in the main search, I moved the sub search to the field where I'm looking for the value.

index=X sourcetype=linux_secure eventtype=sshd_authentication action=success
NOT (host=hostnameA AND user=usernameA
  AND user_src_ip=[ search index=X sourcetype=aws:description source=*:ec2_instances (tags.host=* OR tags.Name=*) earliest=-24h latest=now
    | eval hostName=coalesce('tags.host', 'tags.Name')
    | search hostName=dynamic_ip_hostname
    | sort - _time
    | dedup private_ip_address
    | eval ip="\"".private_ip_address."\""
    | return $ip])
| table _time, user, host, user_src_ip
| sort - _time
| dedup _time user host user_src_ip
| rename _time as Time, user as "Username", host as "Host", user_src_ip as "Source IP"
| convert timeformat="%m-%d-%Y %H:%M:%S" ctime(Time)