How to Top 10 table from Index-A and match hostnames in index-B?

[munisb]

Hi,

I am trying to get the top 10 table from Index-A to have corresponding asset information from Index-B as additional columns.

Hostnames field in index-A is called: HostxA
Hostnames field in index-B is called: HostxB

There are some duplicate entries in both.

Currently my search is able to find top 10 from Index-A and remove the dedups based on IP addresses
however, I am having difficulty using the "HostxA" field "DNS" as an input to find correlating data in index-B

index="indexA" HOSTSUMMARY OS="Windows Server*" | dedup IP | sort -Errors_5 | head 10 | table DNS, IP, Errors_5, Errors_4, Errors_3, Total_Errors

second table:
index="indexB" Hostname=DNS | table Asset-ID, Asset-Tag

Resulting table DNS, IP, Errors_5, Errors_4, Errors_3, Total_Errors, Asset-ID, Asset-Tag

Will appreciate some guidance.

Thanks

[gcusello · Accepted solution]

Hi @munisb@gmail.com,
let me understand, you want all the hostxA from IndexA present also in IndexB as hostxB and take some fields both from IndexA and IndexB, is it correct?

if this is you need, you have to ways.

• use join command,
• use stats command.

First solution is easier to use but has the limit of 50,000 results in the subsearch and it's very slow.
The second one is more structured but it hasn't any limitation and it's quicker.

1)

index="indexA" HOSTSUMMARY OS="Windows Server*" 
| sort 10 -Errors_5 
| join hostxA type=inner [ search index="indexB" Hostname=DNS | rename hostxB AS hostxA ]
| dedup IP
| table DNS IP Errors_5 Errors_4 Errors_3 Total_Errors Asset_ID Asset_Tag

beware: don't use "-" in field names
2)

(index="indexA" HOSTSUMMARY OS="Windows Server*") OR (index="indexB" Hostname=DNS | rename HostxB AS HostxA)
| rename hostxB AS hostxA
| stats values(DNS) AS DNS values(IP) AS IP values(Errors_5) AS Errors_5 values(Errors_4) AS Errors_4 values(Errors_3) AS Errors_3 values(Total_Errors) AS Total_Errors values(Asset_ID) AS Asset_ID values(Asset_Tag) AS Asset_Tag BY hostxA

beware: don't use "-" in field names
Ciao.
Giuseppe

[munisb]

Thank you.

that worked

[493669]

To add additional information from index B there should be one common key field to join both index . Is there any field which is common in both index?