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

[SplunkDash]

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.

[meetmshah]

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!

[ITWhisperer]

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

[SplunkDash]

Hello @ITWhisperer ,

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

[ITWhisperer · Accepted solution]

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