topic Re: How do you find the difference between two different indexes with fields in common? in Splunk Search

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

ashrafshareeb — Fri, 25 Jan 2019 16:16:50 GMT

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.

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

somesoni2 — Fri, 25 Jan 2019 17:07:11 GMT

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

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

ashrafshareeb — Sat, 26 Jan 2019 19:20:19 GMT

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

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

woodcock — Sat, 26 Jan 2019 22:02:34 GMT

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

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

ashrafshareeb — Mon, 28 Jan 2019 09:32:10 GMT

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

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

ashrafshareeb — Mon, 28 Jan 2019 10:03:23 GMT

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

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

ashrafshareeb — Tue, 29 Sep 2020 22:59:46 GMT

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.

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

woodcock — Mon, 28 Jan 2019 18:30:31 GMT

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