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

[Ashwini008]

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?

[ITWhisperer]

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]

@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

[ITWhisperer]

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]

@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

[ITWhisperer]

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

[Ashwini008]

@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

[ITWhisperer · Accepted solution]

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

@ITWhisperer Thank You ! It worked