Splunk Search

How to compare a common field between two indexes and list all values present in one index that are not in the other index?

tp92222
Explorer

Hi,

I have two indexes:
index="abc"

index="dummy"

Now both indexes have one common field ID.

I want to compare index dummy with index abc and list all IDs which are present in index abc, but not in index dummy

eg

index=abc contains

id
2
4
6
8
10

index=dummy contains
1
2
3
4
5
6
7
10

desired result
8

Thanks

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try this

Updated wrong function used in where.

index=abc OR index=dummy | stats values(index) as index by ID | where mvcount(index)=1 AND index="abc"

Update2
For dbquery

index=abc | table ID | eval From="abc" | append [| dbquery ...your query | table ID | eval From="dbquery" ]  | stats values(From) as From by ID | where mvcount(From)=1 AND From="abc"

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try this

Updated wrong function used in where.

index=abc OR index=dummy | stats values(index) as index by ID | where mvcount(index)=1 AND index="abc"

Update2
For dbquery

index=abc | table ID | eval From="abc" | append [| dbquery ...your query | table ID | eval From="dbquery" ]  | stats values(From) as From by ID | where mvcount(From)=1 AND From="abc"
0 Karma

tp92222
Explorer

It worked thank you,

but it only works if both data is coming form index

now ,i am fetching abc data directly form database using dbquery and dummy is same (Index data)

can u suggest query for above condition

0 Karma

tp92222
Explorer
|savedsearch "abc"|table ID "IP Address"|eval From="abc"| append [|savedsearch "dummy"|table ID "IP Address"|eval From="dummy"]|stats values(From) as From by ID|where mvcount(From)=1 AND From="abc"

above query doesnt worked for saved search plz suggest modifications
thank you,

0 Karma

somesoni2
SplunkTrust
SplunkTrust

What's the error do you get? Do the queries work individually?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try the updated answer

0 Karma

sundareshr
Legend

Try
index=abc OR index=dummy | chart count over id by index | where dummy=0

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