topic Compare 2 source types within the same index and find the Gap in Splunk Search

Compare 2 source types within the same index and find the Gap

SplunkDash — Tue, 02 Apr 2024 21:35:30 GMT

Hello,

How do I compare 2 source types within the same index and find the Gap. For Example: index=compare sourcetype=accountA and sourcetype=accountB; we have some account info in accountA but not in accountB and objective is to find that gap.

sourcetypeA

accid nameA addressA cellA

002 test1 tadd1 1234

003 test2 tadd2 1256

003 test2 tadd2 5674

004 test3 tadd3 2345

005 test4 tadd4 4567

006 test5 tadd5 7800

006 test5 tadd5 9900

sourcetypeB

accid nameB addressB cellB

002 test1 tadd1 1234

003 test2 tadd2 5674

004 test3 tadd3 2345

005 test4 tadd3 4567

006 test5 tadd5 9900

Output will be:

003 test2 tadd2 1256

006 test5 tadd5 7800

Any Recommendation will be highly appreciated.

Re: Compare 2 source types within the same index and find the Gap

ITWhisperer — Tue, 02 Apr 2024 22:21:36 GMT

Try something like this

index=compare sourcetype="accountA" OR sourcetype="accountB" | rename nameB as nameA, addressB as addressA, cellB as cellA | eventstats count by accid nameA addressA cellA | where count==1

Compare 2 source types within the same index and find the Gap

SplunkDash — Wed, 03 Apr 2024 03:25:17 GMT

Hello @ITWhisperer ,

Thank for your quick response, truly appreciate it. But it's not working giving the entire events of source type accountA

Re: Compare 2 source types within the same index and find the Gap

meetmshah — Wed, 03 Apr 2024 08:09:44 GMT

Hello @SplunkDash, Can you please check below -

| makeresults | eval _raw="accid,nameA,addressA,cellA 002,test1,tadd1,1234 003,test2,tadd2,1256 003,test2,tadd2,5674 004,test3,tadd3,2345 005,test4,tadd4,4567 006,test5,tadd5,7800 006,test5,tadd5,9900" | multikv forceheader=1 | eval sourcetype="sourcetypeA" | append [| makeresults | eval _raw="accid,nameB,addressB,cellB 002,test1,tadd1,1234 003,test2,tadd2,5674 004,test3,tadd3,2345 005,test4,tadd3,4567 006,test5,tadd5,9900" | multikv forceheader=1 | eval sourcetype="sourcetypeB" ] | kv | stats values(*) as * by accid | where mvcount(nameA) != mvcount(nameB) OR mvcount(addressA) != mvcount(addressB) OR mvcount(cellA) != mvcount(cellB)

Please let me know if you have any questions for the above.

Please accept the solution and hit Karma, if this helps!

Re: Compare 2 source types within the same index and find the Gap

ITWhisperer — Wed, 03 Apr 2024 09:01:47 GMT

Try with coalesce

| eval nameA=coalesce(nameA, nameB), addressA=coalesce(addressA, addressB), cellA=coalesce(cellA, cellB) | eventstats count by accid nameA addressA cellA | where count==1