topic How to join two search using condition if ,case, or where? in Splunk Search

How to join two search using condition if ,case, or where?

Cs80 — Mon, 15 Aug 2022 13:49:13 GMT

Hi there,

I am new to splunk and struggling to join two searches based on conditions .eg. left join with field 1 from index2 if field1!=" " otherwise left join with field 2 from index 2. Field 2 is only present in index 2.and Field 1 is common in

I have two spl giving right result when executing separately . I don't know how to merge both spl based on above condition to get complete result. Thanks

Re: Join two search using condition if ,case or where

richgalloway — Sat, 13 Aug 2022 12:31:11 GMT

There's no such thing as a "conditional join" in Splunk. The join must always be on the same field(s). Any conditions must be satisfied before the join is performed.

<<search 1>> | eval joinField = field1 | join type=left joinField [ <<search 2>> | eval joinField = coalesce(field1, field2) ]

Re: Join two search using condition if ,case or where

PickleRick — Sat, 13 Aug 2022 18:03:52 GMT

As an extension of what @richgalloway already wrote - in Splunk usually join is not the way to go. Try to describe what you have (it's best if you give some examples of the events you have) and what you want to achieve. We'll see what can be done 😉

Re: Join two search using condition if ,case or where

Cs80 — Sat, 13 Aug 2022 20:11:11 GMT

I tried using coalesce but no luck. I am trying to find all domains in our scope using many different indexes and multiple joins. I am currently using two separate searches and both search queries are working fine when executing separately. I want to join both search queries to get complete result. I tried using coalesce with eval Domain in my search where some of public IP are missing .I want to use Private_IP when Public_IP is not there. Then I plan to Join both queries based on Domain.

| eval Domain= coalesce( Public_IP, Private_IP) . After using coalesce command new field Domain showing up empty space as in Public _IP. In other works new filed Domain is replicating Public_IP . I also tried using if with eval bit no luck.

| eval Domain=if(isnull(Public_IP),Private_IP, Public_IP).

Re: Join two search using condition if ,case or where

PickleRick — Sat, 13 Aug 2022 20:34:00 GMT

That would meant that you do not have a field named Private_IP in events which do not have Public_IP. Remember that with Splunk case in field names is important.