How to list non common fields of two table

[ashish9433]

Hi Team,

I am looking to find out a solution where in i have two tables and i am interested in listing out only those fields which are not common.

The below image list out the problem statement along with desired result.

[woodcock · Accepted solution]

For run-anywhere example and solution:

| makeresults
| eval raw="A,1,1:A,10,1:B,2,1:C,3,1:D,4,1:E,5,1:A,1,2:A,11,2:B,2,2:C,3,2:E,5,2:F,6,2:G,7,2"
| fields - _time
| makemv delim=":" raw
| mvexpand raw
| rex field=raw "^(?<Alphabet>[^,]*),(?<No>[^,]*),(?<TableName>[^,]*)$"
| eval TableName="Table" . TableName
| rename COMMENT AS "Everything above is spoofing events, everything below is the actual solution"
| eval AlphabetNo = Alphabet . ":" . No
| eval AlphabetNoTable = AlphabetNo . ":" . TableName
| eval AlphabetNoTableOther = AlphabetNo . ":" . if(TableName="Table1", "Table2", "Table1")
| eventstats values(AlphabetNoTable) AS AlphabetNoTables    
| where AlphabetNoTableOther != AlphabetNoTables
| table Alphabet No TableName
| sort 0 Alphabet TableName No

[ashish9433]

This worked as required, Thanks @woodcock

[gcusello]

Hi ashish9433,

you should build something like this:

search1 
| eval count=0 
| append [ search search2 | stats count by Alfabet, No, TableName]
| stats sum(count) AS check by Alfabet, No, TableName
| where check=0

in this way you list all the groups (Alfabet, No, TableName) of the first search that aren't present in the second one.
If instead you want only the common groups you have tu use | where check>0 in your search.

Beware that the three fields used in stats command must have the same name in both the searches.
If values could have uppercases or lowercases differences, you have to transforms all values in the same case (upper or lower)

Bye.
Giuseppe

[ashish9433]

This option didn't worked for me for some reason the result contained everything of table 1, but i wanted only contents which are non common.

Thanks for your revert though!