Splunk Search

How do you find the difference between two different indexes with fields in common?

ashrafshareeb
Path Finder

Hi all,

I am working on a piece of work on reconciling the trades from DB and a log. I had a thought that the below query should be working fine, but it is not. It has shown be 9K differences if I ran it against Today or yesterday time range. I have checked the query separately to see if the version has changed, but it's the same version and same trade when I ran the 2 searches separately.

| set diff [search index=stdb sourcetype=stdbtype  
| dedup TRADEID sortby -AUD_VER
| rename TRADEID as tradeId,AUD_VER as SMTVersion    
| table tradeId, SMTVersion]
[search index=XXX_inbound SMT55/BOND_TR  
| dedup tradeId sortby -SMTVersion
| table tradeId, SMTVersion]

If I investigate a few trades, the version and the trade ID are the same, but it shows as a difference in the above query.

I'm not sure why and I'm pretty much confused.

Any help is much appreciated.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

I would try like this (your query uses subsearches which have limitations so try this version)

( index=stdb sourcetype=stdbtype  ) OR (index=XXX_inbound SMT55/BOND_TR  )
| fields TRADEID AUD_VER tradeId SMTVersion index
| eval tradeId=coalesce(TRADEID, tradeId)
| eval SMTVersion=coalesce(SMTVersion,AUD_VER )
| stats dc(index) as indexes by tradeId SMTVersion
| where indexes=1 

We basically select data from both indexes in same base search, create common fields based on field values available (since they have different names in different indexes, that eval-coalesce will create a field with common name for event from both indexes). The stats will check how many indexes the (combined) tradeId SMTVersion combinations are reporting to. The last where clause selects only the tradeId SMTVersion combinations which are only reported on one index (not both, else the indexes value will be 2).

View solution in original post

woodcock
Esteemed Legend

Like this:

(index=stdb sourcetype=stdbtype) OR (index=XXX_inbound SMT55/BOND_TR)
| rename TRADEID AS tradeId, AUD_VER AS SMTVersion
| stats dc(index) AS index_count values(index) AS index_values BY tradeId SMTVersion
| where index_count < 2

ashrafshareeb
Path Finder

While trying the above query - I'm getting Error in 'stats' command: The argument 'index_values' is invalid.

0 Karma

woodcock
Esteemed Legend

I forgot an AS. I edited my answer and fixed it.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

I would try like this (your query uses subsearches which have limitations so try this version)

( index=stdb sourcetype=stdbtype  ) OR (index=XXX_inbound SMT55/BOND_TR  )
| fields TRADEID AUD_VER tradeId SMTVersion index
| eval tradeId=coalesce(TRADEID, tradeId)
| eval SMTVersion=coalesce(SMTVersion,AUD_VER )
| stats dc(index) as indexes by tradeId SMTVersion
| where indexes=1 

We basically select data from both indexes in same base search, create common fields based on field values available (since they have different names in different indexes, that eval-coalesce will create a field with common name for event from both indexes). The stats will check how many indexes the (combined) tradeId SMTVersion combinations are reporting to. The last where clause selects only the tradeId SMTVersion combinations which are only reported on one index (not both, else the indexes value will be 2).

ashrafshareeb
Path Finder

Hi Somesoni2,

Thanks for the query, I want to make sure the tradeid in index 1 is available in index 2, basically no difference between first index and second index.
If there is a difference, then it means the tradeid's are not available in either of the index. will where clause indexes=2 should be used here? . just only the differences should be shown if there is no difference no results - - all good. if there is a difference only those trades needs to be shown -- need to check those trades

let me know if I'm not clear

0 Karma

ashrafshareeb
Path Finder

Hi Somesoni2,

The query was useful with indexes=1 where the trades in one index is not available in the other index is quite helpful. I have found that the DBConnect has not pulled few trades. Thanks a lot for the query

0 Karma

ashrafshareeb
Path Finder

Hi Somesoni2,

I have few trades that are available in both the indexes but still appears in the above query.
index=XXX_inbound SMT55/BOND_TR has multiple version, I just want to take the latest versions and compare against the first index.

For eg: 0001414386
The trade is available in index1, as version 4
But the same trade is available in index2, as version 3 and version 4. I just want to take the latest version and compare it against index 1.

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