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

Abhineet — Wed, 12 Jan 2022 14:54:18 GMT

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.

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

ITWhisperer — Wed, 12 Jan 2022 15:08:07 GMT

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"

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

Abhineet — Wed, 12 Jan 2022 15:14:47 GMT

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

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

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

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

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