Splunk Search

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

New Member

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 (Afieldmatch)
Index B has matching field (Bfieldmatch)

Both Indexes have index specific fields I would like to add together in a table for true enrichment of the data:
Index A has Ainterestingfield1 Ainterestingfield2 Ainterestingfield3 Ainterestingfield4

Index B has Binterestingfield1 Binterestingfield2 Binterestingfield3 Binterestingfield4

Each Index has very helpful fields that I can search on to remove false positives if I can match on Afieldmatch and Bfieldmatch 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

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 afield as bfield

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 afield as bfield | stats values(ainterestingfield) values(binterestingfield) 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 afield as bfield

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 afield as bfield | stats values(ainterestingfield) values(binterestingfield) 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

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 - Afieldmatch (matchfield)
Index B - B
fieldmatch (matchfield)

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`
0 Karma

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

Contributor

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

(index=A)

|rename Afieldmatch as Bfieldmatch
| fields Bfieldmatch
| join Bfieldmatch
[ search index=B ]
| table Ainterestingfield1 Ainterestingfield2 Ainterestingfield3 Ainterestingfield4 Binterestingfield1 Binterestingfield2 Binterestingfield3 Binterestingfield4

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