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

[rafaelvianaalve]

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.

[sundareshr · Accepted solution]

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

[rafaelvianaalve]

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

[cb_usps]

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.

So maybe try the following:
index=IndexA | where HIERARCHY="1" |rename CN AS Site | join Site type=left [search index="IndexB" | dedup "Serial Number"| 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"

[rafaelvianaalve]

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?