compare 2 lookups contents

[spisiakmi]

Hi, any support please.

I have 2 lookups.

tmp1_1.csv

WorkplaceId,PK1,Description,Contract
1234567890,7535712,Contract1,19
1123456789,7535712,Contract2,18
1234567890,7456072,Contract3,14
1234567890,7456072,Contract4,15
1234567891,7456072,Contract5,16

tmp1_2.csv

WorkplaceId,State,Timestamp,ContractId
1234567890,Start,1752838050,12
1234567890,End,1752838633,12
1123456789,Start,1752838853,13
1123456789,Break,1752839380,13
1123456789,End,1752839691,13
1234567890,Start,1752839720,14
1234567890,Start,1752839745,15
1234567891,Start,1752839777,16
1234567891,Start,1752839790,18
1234567890,Start,1752839892,19

The primary key between these tables is
WorkplaceId,Contract=WorkplaceId,ContractId

The task is always to select the content from tmp1_2.csv based on conditions

1. cond1: select everything from tmp1_2.csv where WorkplaceId,Contract!=WorkplaceId,ContractId. In this case the result should be

WorkplaceId,State,Timestamp,ContractId
1234567890,Start,1752838050,12
1234567890,End,1752838633,12
1123456789,Start,1752838853,13
1123456789,Break,1752839380,13
1123456789,End,1752839691,13

2. cond2: select everything from tmp1_2.csv where WorkplaceId,Contract=WorkplaceId,ContractId. In this case the result should be

WorkplaceId,State,Timestamp,ContractId
1234567890,Start,1752839720,14
1234567890,Start,1752839745,15
1234567891,Start,1752839777,16
1234567891,Start,1752839790,18
1234567890,Start,1752839892,19

Any support, please?

[PrewinThomas · Accepted solution]

@spisiakmi 

You want 2 different searches for your conditions like below?

Condition 1: Select rows from tmp1_2.csv where (WorkplaceId,ContractId) is NOT present in tmp1_1.csv as (WorkplaceId,Contract)

| inputlookup tmp1_2.csv
| eval key=WorkplaceId."-".ContractId
| lookup tmp1_1.csv WorkplaceId as WorkplaceId, Contract as ContractId OUTPUT PK1
| where isnull(PK1)
| table WorkplaceId, State, Timestamp, ContractId

Condition 2: Select rows from tmp1_2.csv where (WorkplaceId,ContractId) is present in tmp1_1.csv as (WorkplaceId,Contract)

| inputlookup tmp1_2.csv
| eval key=WorkplaceId."-".ContractId
| lookup tmp1_1.csv WorkplaceId as WorkplaceId, Contract as ContractId OUTPUT PK1
| where isnotnull(PK1)
| table WorkplaceId, State, Timestamp, ContractId

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!

[spisiakmi]

Hi PrewinThomas

thank you very much. Works fantastic.