How to merge two searches without losing main search data?

[jeremyrenard]

Hi,

I am having some troubles to merge two searches and I am looking for the best way to do this. 
We have firewall traffic with NAT that is made on two levels. My goal is to be able to identify the flow with original and nated ip addresses. I explain :

FW1 : src1,dst1,xlatesrc1,xlatedst1

FW2 : src2 (=xlatescr1), dst2 (=xlatedst1), xlatedst2

goal = table : src1,dst1,xlatesrc1,xlatedst1 (=xlatedst2 if it exists, xlatedst1 instead)

I have made something like:

search_FW1
| stats by src1,dst1,xlatesrc1,xlatedst1
| join left=[ search search_FW2
| stats values(xlatedst2) as xlatedst1 by src2]
| rename src2 as xlatesrc1
| table src1,dst1,xlatesrc1,xlatedst1

But I have noticed that if src2 does not exist in search_FW1, I loose the event from my main search (search_FW1) :(. I thought that the "left" parameter of "join" should solve the issue, but it does not... 

Any idea how to avoid it (and maybe optimize my search as I have seen that "join" has poor performance)?
Thanks

[jeremyrenard]

Hello, thank you for your replies. I am working on it. I will let you know as soon as I have achieved what I att

empt to do 🙂

[gcusello · Accepted solution]

Hi @jeremyrenard,

you should try to use the eval-coalesce command and use stats instead join that's very slow, something like this:

At first you have to identify (from your shared search isn't possible) the joining key (present in both the searches) and use it in the stats command, if they are xlatesrc1 and xlatedst1, you could try:

<search_FW1> OR <search_FW2>
| eval 
   xlatesrc1=coalesce(xlatesrc1,src2), 
   xlatedst1=coalesce(xlatedst1, dst2)
| stats values(src1) AS src1 values(dst1) AS dst1 BY xlatesrc1 xlatedst1

 Ciao.

Giuseppe

[jeremyrenard]

I have got it working (needed to add extra search to remove dupplicated "xlatesrc" values but work as a charm).

Thank you 🙂 

[gcusello]

Hi @jeremyrenard,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors 😉

[isoutamo]

Did this https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-joi... helps you to format correctly your SPL?
r. Ismo