Splunk Search

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

munisb
Explorer

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

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

munisb
Explorer

Thank you.

that worked

0 Karma

493669
Super Champion

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?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...