Splunk Search

How to search two Indexes based on matching fields and add fields from both indexes to a table

jrindfleisch
Observer

I'm new to splunk And i'm trying to add some logic to reduce false positives.

I have two indexes
Index=A
index=B

Both indexes have a field that has the same data I can match on:
Index A has a field (A_field_match)
Index B has matching field (B_field_match)

Both Indexes have index specific fields I would like to add together in a table for true enrichment of the data:
Index A has A_interesting_field_1 A_interesting_field_2 A_interesting_field_3 A_interesting_field_4

Index B has B_interesting_field_1 B_interesting_field_2 B_interesting_field_3 B_interesting_field_4

Each Index has very helpful fields that I can search on to remove false positives if I can match on A_field_match and B_field_match from both indexes.

I have tried transaction, stats and join but I am completely lost and getting nowhere.
Any help would be greatly appreciated.

0 Karma

acfecondo75
Path Finder

Run your search to retrieve events from both indexes (and add whatever additional criteria there is, if any)

index=a OR index=b

Now, if the field that you want to aggregate your events on is NOT named the same thing in both indexes, you will need to normalize it. To do this, just rename the field from index a to the same name the field uses in index b (or vice versa or to a new field. Doesn't matter as long as they're both called the same thing). If they already share a name, skip this.

index=a OR index=b | rename a_field as b_field

Once the field you are grouping on is normalized, feed your results into a stats command that groups by the field that needs to match. You'll have to use values() or other stats functions to get the additional fields to show up the way you want them to.

index=a OR index=b | rename a_field as b_field | stats values(a_interesting_field) values(b_interesting_field) by matching_field

You can pipe these results into additional search or where commands to narrow the results. However, keep in mind, if you can narrow the results in your initial search criteria, that's always preferred.,Run your search to retrieve events from both indexes (and add whatever additional criteria there is, if any)

index=a OR index=b

Now, if the field that you want to aggregate your events on is NOT named the same thing in both indexes, you will need to normalize it. To do this, just rename the field from index a to the same name the field uses in index b (or vice versa or to a new field. Doesn't matter as long as they're both called the same thing). If they already share a name, skip this.

index=a OR index=b | rename a_field as b_field

Once the field you are grouping on is normalized, feed your results into a stats command that groups by the field that needs to match. You'll have to use values() or other stats functions to get the additional fields to show up the way you want them to.

index=a OR index=b | rename a_field as b_field | stats values(a_interesting_field) values(b_interesting_field) by matching_field

You can pipe these results into additional search or where commands to narrow the results. However, keep in mind, if you can narrow the results in your initial search criteria, that's always preferred.

0 Karma

vasanthmss
Motivator

Make sure that your index (A) , Index (B) have the matching filed with unique name, if not use the field alias to keep the same field name.

Index A - A_field_match (match_field)
Index B - B_field_match (match_field)

Once you have the field alias setup the field match_field will be common for both the index.

Standalone search to produce index A results

| stats c | fields - c | eval index="a" | eval match_field="match"|  eval A_interesting_field_1="A_interesting_field_1"|  eval A_interesting_field_2="A_interesting_field_2"|  eval A_interesting_field_3="A_interesting_field_3"

Standalone search to produce index B results

| stats c | fields - c | eval index="b" | eval match_field="match"|  eval B_interesting_field_1="B_interesting_field_1"|  eval B_interesting_field_2="B_interesting_field_2"|  eval B_interesting_field_3="B_interesting_field_3"

Here is the sample search,

| stats c | fields - c | eval index="a" | eval match_field="match"|  eval A_interesting_field_1="A_interesting_field_1"|  eval A_interesting_field_2="A_interesting_field_2"|  eval A_interesting_field_3="A_interesting_field_3"
| append 
    [| stats c | fields - c | eval index="b" | eval match_field="match"|  eval B_interesting_field_1="B_interesting_field_1"|  eval B_interesting_field_2="B_interesting_field_2"|  eval B_interesting_field_3="B_interesting_field_3"]
| stats values(*) as * by match_field

So your search looks something like this,

   (index=<index a> [filters for a if any]) OR (index=<index b> [filters for b if any]) .. |table required fields .... | stats values(*) as * by `match_field`
V
0 Karma

kiamco
Path Finder

can you provide a sample data for both indexes? my first though would be using a join but since you mentioned that the join is not working I am curious on what the data looks like

0 Karma

sandeepmakkena
Contributor

I have a similar use case hope this works for you as well.

(index=A)

|rename A_field_match as B_field_match
| fields B_field_match
| join B_field_match
[ search index=B ]
| table A_interesting_field_1 A_interesting_field_2 A_interesting_field_3 A_interesting_field_4 B_interesting_field_1 B_interesting_field_2 B_interesting_field_3 B_interesting_field_4

As you are using two indexes, use give as many filters as possible like source type or source and run it on a small-time range to check the results.

Thanks.

0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...