All Apps and Add-ons

How to compare all the columns from table A with Table B and show if there are any mismatches between both the Tables?

Ashwini008
Contributor

Hi ,

I have two tables ,i need to compare result of each column of table A with Table B and if there is any mismatch then i need to show those results

EXAMPLE:

Table A

ID

NAME

TYPE

1

TESLA

VARCHAR

2

SWIFT

INT

 

TABLE B

ID

NAME

TYPE

1

TESLA

INT

2

SWIFT

INT

 

Result:

IDA

NAMEA

TYPEA

IDB

NAMEB

TYPEB

1

TESLA

VARCHAR

1

TESLA

INT

 

Any suggestions please?

0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults
| eval _raw="Column_id,Column_name,Data_length,Data_type,DBName,Table
1,MAND,3,VARCHAR2,HYP,BNAK
1,MAND,3,VARCHAR2,HYP,HYP100
1,MAND,3,VARCHAR2,HYP,HYP101"
| multikv forceheader=1 
| eval table="A" 
| append
    [| makeresults
    | eval _raw="Column_id,Column_name,Data_length,Data_type,DBName,Table
1,MAND,2,VARCHAR2,HPR,BNAK
1,MAND,3,INT,HPR,HYP100
1,MAND,3,VARCHAR2,HPR,HYP101"
    | multikv forceheader=1 
    | eval table="B"]
| table Column_id,Column_name,Data_length,Data_type,DBName,Table




| eventstats count by Column_id Column_name Data_length Data_type Table
| where count=1

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
search table A
| eval table="A"
| append [search table B | eval table="B"]
| dedup id name type
| stats count values(table) as table by id name type
| where count=1
| eval nameA=if(table="A",name,null())
| eval typeA=if(table="A",type,null())
| eval nameB=if(table="B",name,null())
| eval typeB=if(table="B",type,null())
| stats values(nameA) as nameA values(typeA) as typeA values(nameB) as nameB values(typeB) as typeB by id

Ashwini008
Contributor

@ITWhisperer Thank you for the response.

If i use the above query then i get result as below

Result:

IDA

NAMEA

TYPEA

IDB

NAMEB

TYPEB

1

TESLA

VARCHAR

1

TESLA

INT

2

SWIFT

INT

 

 

 

 

Here i get result of the mismatch plus the table A Data. But i need to get only the mismatch value. Can you suggest

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
search table A
| eval table="A"
| append [search table B | eval table="B"]
| stats count values(table) as table by id name type
| where count=1
| eval nameA=if(table="A",name,null())
| eval typeA=if(table="A",type,null())
| eval nameB=if(table="B",name,null())
| eval typeB=if(table="B",type,null())
| stats values(nameA) as nameA values(typeA) as typeA values(nameB) as nameB values(typeB) as typeB by id

Remove the dedup - or did you only want the mismatched fields e.g. type in this instance?

Ashwini008
Contributor

@ITWhisperer YES basically i want to compare each cloumn of both the csv and if there is any mismatch between any of the values in any columns then i need to show only those mismatch vales.Can you please help

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
search table A
| eval table="A"
| append [search table B | eval table="B"]
| stats count values(table) as table by id name type
| where count=1
| eval nameA=if(table="A",name,null())
| eval typeA=if(table="A",type,null())
| eval nameB=if(table="B",name,null())
| eval typeB=if(table="B",type,null())
| stats values(name) as names values(type) as types values(nameA) as nameA values(typeA) as typeA values(nameB) as nameB values(typeB) as typeB by id
| eval nameA=if(mvcount(names)!=1,nameA,null())
| eval typeA=if(mvcount(types)!=1,typeA,null())
| eval nameB=if(mvcount(names)!=1,nameB,null())
| eval typeB=if(mvcount(types)!=1,typeB,null())
| table id nameA typeA nameB typeB
0 Karma

Ashwini008
Contributor

@ITWhisperer Thank you for the response.Sorry for the confusion. The below is my actual data from two lookups

Lookup1

Column_id

Column_name

Data_length

Data_type

DBName

Table

1

MAND

3

VARCHAR2

HYP

BNAK

1

MAND

3

VARCHAR2

HYP

HYP100

1

MAND

3

VARCHAR2

HYP

HYP101

 

Lookup2

Column_id

Column_name

Data_length

Data_type

DBName

Table

1

MAND

2

VARCHAR2

HPR

BNAK

1

MAND

3

INT

HPR

HPR100

1

MAND

3

VARCHAR2

HPR

HPR101

Here i want to compare table BNAK OF columnid 1 with second lookup where table is BNAK AND COLUMNID IS 1.Similary for other table and column ID as well.

So my result should be 

Column_id

Column_name

Data_length

Data_type

DBName

Table

1

MAND

3

VARCHAR2

HYP

BNAK

1

MAND

2

VARCHAR2

HPR

BNAK

1

MAND

3

VARCHAR2

HYP

HYP100

1

MAND

3

INT

HPR

HPR100

 

Please suggest

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults
| eval _raw="Column_id,Column_name,Data_length,Data_type,DBName,Table
1,MAND,3,VARCHAR2,HYP,BNAK
1,MAND,3,VARCHAR2,HYP,HYP100
1,MAND,3,VARCHAR2,HYP,HYP101"
| multikv forceheader=1 
| eval table="A" 
| append
    [| makeresults
    | eval _raw="Column_id,Column_name,Data_length,Data_type,DBName,Table
1,MAND,2,VARCHAR2,HPR,BNAK
1,MAND,3,INT,HPR,HYP100
1,MAND,3,VARCHAR2,HPR,HYP101"
    | multikv forceheader=1 
    | eval table="B"]
| table Column_id,Column_name,Data_length,Data_type,DBName,Table




| eventstats count by Column_id Column_name Data_length Data_type Table
| where count=1

Ashwini008
Contributor

@ITWhisperer Thank You ! It worked

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...