topic Re: Merging two searches without loosing main search data in Splunk Search

How to merge two searches without losing main search data?

jeremyrenard — Fri, 26 Aug 2022 11:01:27 GMT

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:

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

Re: Merging two searches without loosing main search data

isoutamo — Fri, 26 Aug 2022 10:04:48 GMT

Did this https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-join-and-the/m-p/391288/thread-id/113948 helps you to format correctly your SPL?
r. Ismo

Re: Merging two searches without loosing main search data

gcusello — Fri, 26 Aug 2022 11:13:05 GMT

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

Re: How to merge two searches without losing main search data?

jeremyrenard — Fri, 26 Aug 2022 13:59:48 GMT

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 🙂

Re: Merging two searches without loosing main search data

jeremyrenard — Fri, 26 Aug 2022 14:47:38 GMT

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

Thank you 🙂

Re: Merging two searches without loosing main search data

gcusello — Sat, 27 Aug 2022 08:45:23 GMT

Hi @jeremyrenard,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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