I want to compare (OWNER)(TABLE_NAME) to (OWNER_New)(TABLE_NAME_New). And once the value matched then want to find difference between Size_In_MB_new and Size_In_MB.
Catch is i do not want to only compare the value of (OWNER)(TABLE_NAME) in same row, i want it to search for the same value in all row of (OWNER_New)(TABLE_NAME_New) and if match found then perform difference of desired fields.
OWNER | TABLE_NAME | Size_In_MB | OWNER_New | TABLE_NAME_New | Size_In_MB_New |
MZOWNER | SYSTEM_LOG | 121620 | RB_CUSTOM | IPGINVOICETRANSACTIONARCHIVE | 158115 |
GENEVA_ADMIN | IPGEMSV5US | 89920 | MZOWNER | SYSTEM_LOG | 121620 |
GENEVA_ADMIN | IPGEMSV7US | 53696 | GENEVA_ADMIN | IPGEMSV5US | 89920 |
GENEVA_ADMIN | EVENTMAPPINGSETVALUE | 38656 | GENEVA_ADMIN | IPGEMSV7US | 53696 |
GENEVA_ADMIN | IPGEMSV5GB | 14528 | GENEVA_ADMIN | EVENTMAPPINGSETVALUE | 38720 |
RB_CUSTOM | IPGINVOICEGROUPARCHIVE | 13414 | RB_CUSTOM | IPGNONINVOICETRANARCHIVE | 31617 |
GENEVA_ADMIN | IPGEMSV7GB | 12352 | GENEVA_ADMIN | IPGEMSV5GB | 14528 |
GENEVA_ADMIN | IPGEMSV7IT | 11712 | RB_CUSTOM | IPGINVOICEGROUPARCHIVE | 13606 |
GENEVA_ADMIN | IPGEMSV7MY | 10688 | GENEVA_ADMIN | IPGEMSV7GB | 12352 |
GENEVA_ADMIN | CUSTPRODUCTCHARGE | 10378 | GENEVA_ADMIN | IPGEMSV7IT | 11712 |
Try this way, it works as I am using similar joins.
Fields names in both tables should be same otherwise rename them using "rename " command
index=abc source=abc
| join type=inner owner,table_name [ search index=xyz source=xyz
| stats Size_In_MB_New]
| stats owner,table_name,Size_In_MB,Size_In_MB_New
| eval DiffSize= Size_In_MB -Size_In_MB_New