Use the output of search A to refine results in search B

[vmeleco]

I have 2 searches.

Search A produces a table output of "UserIP"

Search B produces a table output of "FailedDestinationIP' and "FailedSourceIP". (search will have both values reported)

I want to see if "UserIP" matches either "FailedDestinationIP" or "FailedSourceIP" in search B. If it matches "Failed Destination" then I want output of "FailedSourceIP" or if it matches "FailedSourceIP" I want output of "FailedDestinationIP".

It's ok if this is broken into to searches, rather than performing in one operation, as I really want 2 list of of either FailedsourceIP or Failed DestinationIP, where UserIP matches the opposite.

I have tried some join commands and eval commands, but my results are not correct.

Search A:
source = abc | rex ".UserIP (?\S+)" | rex "(role(s):\s|role\s)(?\S+)." | rex "AuthGroup: (?\S+)" | search roles=$RoleName$ | table UserIP | dedup UserIP

Search B:
source = xyz | rex "unnel (?\S+) failed to (?\S+)" | table FailedDestinationIP FailedSourceIP

Seems simple, but I can't get it right. Thanks in advance!

[woodcock]

Like this:

(index="abcIndex" AND sourcetype="abcSourcetype" AND source = "abc")
OR (index="xyzIndex" AND sourcetype="xyzSourcetype" AND source = "xyz")
| rex <ALL OF YOUR REX STUFF HERE (you did not protect it so answers garbled it>
| search sourcetype="xyz" OR (sourcetype="abc" AND roles=$RoleName$)

| eval UserIP = coalesce(UserIP, mvappend(FailedDestinationIP, FailedSourceIP))
| stats values(Failed*) AS Failed* dc(src) AS src_count BY UserIP
| where src_count=="2"
| fields - src_count
| eval importantIP = case(
   UserIP==FailedDestinationIP, FailedSourceIP,
   UserIP==FailedSourceIP, FailedDestinationIP, 
   true(), "ERROR")

Run-anywhere example:

| makeresults 
| eval UserIP = "a b c d e f g", src="abc"
| makemv UserIP
| mvexpand UserIP
| append [|makeresults
| eval FailedDestinationIP = "a c g x q p l", src="xyz"
| makemv FailedDestinationIP
| mvexpand FailedDestinationIP
| eval FailedSourceIP = "p n b d e f z"
| makemv FailedSourceIP
| streamstats count
| eval FailedSourceIP = mvindex(FailedSourceIP, count - 1) ]
| fields - count

| eval UserIP = coalesce(UserIP, mvappend(FailedDestinationIP, FailedSourceIP))
| stats values(Failed*) AS Failed* dc(src) AS src_count BY UserIP
| where src_count=="2"
| fields - src_count
| eval importantIP = case(
   UserIP==FailedDestinationIP, FailedSourceIP,
   UserIP==FailedSourceIP, FailedDestinationIP, 
   true(), "ERROR")

[to4kawa]

source="xyz" OR source="abc" 
| rex "tunnel (?<FailedSourceIP>\S+) failed to (?<FailedDestinationIP>\S+)"
| rex ".UserIP (?<UserIP>\S+)" | rex "(role(s):\s|role\s)(?<RoleName>\S+)." | rex "AuthGroup: (?<AuthGroup>\S+)" 
| eval dest_ip=coalesce(FailedDestinationIP,UserIP), src_ip=coalesce(FailedSourceIP,UserIP)
| stats values(UserIP) as UserIP, values(FailedSourceIP) as FailedSourceIP
, values(FailedDestinationIP) as FailedDestinationIP, dc(source) as flag
, values(RoleName) as RoleName, values(AuthGroup) as AuthGourp by src_ip dest_ip
| where flag > 1

Some query strings is missing. please check your POST message.

and, I don't know your logs and field extraction. please make your table by yourself.

[vmeleco]

FYI
search A is less 100 results.
search B can be large, but less then 10000.

[richgalloway]

If you have fewer than 10,000 events in Search A, try using a subsearch. If you have more than that many event, but less than 10,000 in Search B, swap put Search A in the subsearch.

source = xyz | rex "unnel (?\S+) failed to (?\S+)" 
| fields FailedDestinationIP FailedSourceIP 
| search [source = abc | rex ".UserIP (?\S+)"  
  | stats count by UserIP 
  | eval FailedDestinationIP=UserIP, FailedSourceIP=UserIP 
  | fields FailedDestinationIP FailedSourceIP | format ]

[vmeleco]

no go. after adding search parameter to the second source, no results found. I know there are matches when I run the searches separately and compare.

[vmeleco]

"Where" command is malformed.

[richgalloway]

I updated the answer to use search instead.