topic Re: How to edit my join search to include all events from two indexes in search results? in Splunk Search

How to edit my join search to include all events from two indexes in search results?

rafaelvianaalve — Tue, 10 May 2016 13:56:38 GMT

I have two indexes with digital certificate information ( indexA and IndexB ). I used the join command to add some fields that the index does not have through the common field Site.

I need to make a merge between the two indexes as there are data that only the IndexA contains, and others that only contains IndexB .

Currently this syntax:

index=IndexA  | where HIERARCHY="1" |rename CN AS Site| join Site type=left max=0 [search index="IndexB" | dedup _raw | rename "Certification object" AS Site|fields Site,"Expiry Date","CI Name","Serial Number",Environment,SupplierName] |dedup Hostname Site  "Valid to" TIMESTAMPENTRADA2 | table Hostname Site "Expiry Date"

I tried the option type=outer, but remained the same result.

I also tried to use index=indexA OR index=IndexB, but didn't work.

Re: How to edit my join search to include all events from two indexes in search results?

cb_usps — Tue, 10 May 2016 20:09:04 GMT

What exactly isn't working with the join? You are aware that even when choosing an outer/left join that only events in indexB which match events in indexA will be included in the join, correct? If you want both complete indexes in the results, then you should pick a different merge command.

The 'dedup _raw' in your subsearch may be the problem. My experiments generated missing data when I did the same. Instead I ran a dedup on an extracted field, and all the values joined in the final results. This behavior may be related to the 'earlier' option to join.
By default, 'join' matches the main search results only against earlier results from the subsearch. Set earlier=false if your indexB has newer timestamps than indexA.

Re: How to edit my join search to include all events from two indexes in search results?

rafaelvianaalve — Wed, 11 May 2016 12:03:54 GMT

Exactly, the join this returning only the data that have match with indexB , but I need to bring all the data even if not this match , join index (full) . Any idea?

Re: How to edit my join search to include all events from two indexes in search results?

sundareshr — Wed, 11 May 2016 12:27:22 GMT

Try this

(index=A AND HIERARCHY="1") OR index=B | eval Site=coalesce("Certification object", CN) | stats last(Hostname) as Hostname, last("Expiry Date") as "Expiry Date" by Site

Re: How to edit my join search to include all events from two indexes in search results?

rafaelvianaalve — Wed, 11 May 2016 14:46:43 GMT

Thanks , I used the "stats " + " append" and it worked. however still need to make some adjustments as the index exists the Hostname field and not B , that is, the data came as soon as there is the same certificate on different servers (Load Balancer 😞

Certificate Hostname Valid to
Cert1 AA1 24/12/2016
BB1
Cert2 AA2 01/12/2016
BB2