Splunk query to create field which should be true or false if data field value available in lookup table.

[Abhineet]

Hi

I am trying to create new column in table after extracting information from json data, new column have value "True" or "False" if "toDomain" column data available in  lookup table .

Querry: index="pps_index" sourcetype="pps_messagelog" "filter.routeDirection"=outbound |rex field=envelope.rcpts{} .*@(?<toDomain>.*)|rex field=envelope.from .*@(?<fromDomain>.*)|rename envelope.from as Sender envelope.rcpts{} as Recipient msg.header.subject as Subject msgParts{}.detectedName as Attachment | table Sender Recipient Subject Attachment toDomain

lookup file "publicDomain.csv" contains below data as example.

publicDomain
123.com
123box.net
123india.com
123mail.cl
123qwe.co.uk
126.com
15meg4free.com
163.com
163.net
169.cc
188.net

current output:

Sender	Recipient	Subject	Attachment	toDomain
Ruotong_Yin@contractor.amat.com	ngarza@littelfuse.com	RE: AMAT PO 4513405497 11.26.2021 Littelfuse Inc.	text.txt text.html lt po# 4513405497.pdf	littelfuse.com
Amanda_Mo@amat.com	cod.b2b.servicerequest@my344310.mail.crm.ondemand.com	RE: [ Ticket: 3018517 ] WF: WF: 25420987000020 & 25420672000020-- 0190-17499W * (1+1) =2EA--- pls create STO from 8665 & 8639 to 8602. thank you!	text.txt text.html image005.jpg image006.png image001.jpg image002.jpg image007.jpg	my344310.mail.crm.ondemand.com
Amanda_Mo@amat.com	hfamat.list@bondex.com.cn	RE: [ Ticket: 3018517 ] WF: WF: 25420987000020 & 25420672000020-- 0190-17499W * (1+1) =2EA--- pls create STO from 8665 & 8639 to 8602. thank you!	text.txt text.html image005.jpg image006.png image001.jpg image002.jpg image007.jpg	bondex.com.cn
tme@massgroup.com	tme@123box.net	Work Order Past Due Notification: WO# 199996	text.txt	123box.net

Desired Output:

Sender	Recipient	Subject	Attachment	toDomain	PDVal
Ruotong_Yin@contractor.amat.com	ngarza@littelfuse.com	RE: AMAT PO 4513405497 11.26.2021 Littelfuse Inc.	text.txt text.html lt po# 4513405497.pdf	littelfuse.com	False
Amanda_Mo@amat.com	cod.b2b.servicerequest@my344310.mail.crm.ondemand.com	RE: [ Ticket: 3018517 ] WF: WF: 25420987000020 & 25420672000020-- 0190-17499W * (1+1) =2EA--- pls create STO from 8665 & 8639 to 8602. thank you!	text.txt text.html image005.jpg image006.png image001.jpg image002.jpg image007.jpg	my344310.mail.crm.ondemand.com	False
Amanda_Mo@amat.com	hfamat.list@bondex.com.cn	RE: [ Ticket: 3018517 ] WF: WF: 25420987000020 & 25420672000020-- 0190-17499W * (1+1) =2EA--- pls create STO from 8665 & 8639 to 8602. thank you!	text.txt text.html image005.jpg image006.png image001.jpg image002.jpg image007.jpg	bondex.com.cn	False
tme@massgroup.com	tme@123box.net	Work Order Past Due Notification: WO# 199996	text.txt	123box.net	True

Kindly provide solution to resolve issue.

[ITWhisperer]

Add a second column to your csv file called PDVal and set all the rows to "True". After the lookup, you can fillnull value="False" PDVal to set all the unmatched to "False"

[Abhineet]

Thanks for solution.. will try and check whether it works or not.