topic Re: splunk lookup search two columns csv in Splunk Search

How to create a search for lookup search two columns csv?

sulaimancds — Wed, 02 Aug 2023 19:26:24 GMT

index=mail [ | inputlookup email_users.csv | rename address AS query | fields query ]
| dedup MessageTraceId
| lookup email_domain_whitelist domain AS RecipientDomain output domain as domain_match
| where isnull(domain_match)
| lookup all_email_provider_domains domain AS RecipientDomain output domain as domain_match2
| where isnotnull(domain_match2)
| stats values(RecipientAddress) as Recipient values(Subject) as Subject earliest(_time) AS "Earliest" latest(_time) AS "Latest" values(Status) as Status by RecipientDomain SenderAddress
| eval subject_count=mvcount(Subject)
| sort - subject_count
| convert ctime("Latest")
| convert ctime("Earliest")

I have a csv call email_user.csv. There are 2 columns, 1 is address another is event date.

Afer the above query has been done, there should be a few results.

On those results , it matches the list from address column. I want to also show the event date column from the csv which matches the result.

Please help.

Re: splunk lookup search two columns csv

ITWhisperer — Tue, 18 Jul 2023 05:37:20 GMT

It looks like your results could have two addresses, either or both of which could have matches in your lookup file so you would have to do two lookups, for example

| lookup email_users.csv address AS SenderAddress

Re: splunk lookup search two columns csv

sulaimancds — Tue, 18 Jul 2023 05:49:43 GMT

now the query is working , how to show the column of event date in the results as well , after the query is finish and results has been shown

Re: splunk lookup search two columns csv

ITWhisperer — Tue, 18 Jul 2023 05:52:11 GMT

Which date do you mean? Your stats already has earliest and latest

Re: splunk lookup search two columns csv

sulaimancds — Tue, 18 Jul 2023 05:59:30 GMT

I have a csv call email_user.csv. There are 2 columns, 1 is address another is event date.

i want to show event date in the results as well. event date is from the csv.

event_date

2/10/2023

1/10/2023

30/9/2023

23/9/2023

8/9/2023

Re: splunk lookup search two columns csv

ITWhisperer — Tue, 18 Jul 2023 06:05:38 GMT

Your search returns these columns: Recipient, Subject, Earliest, Latest, Status, RecipientDomain, SenderAddress and subject_count - which of these is the event_date?

Re: splunk lookup search two columns csv

sulaimancds — Tue, 18 Jul 2023 06:12:11 GMT

i want to include event date as well, it is from the csv , please help me for that

Re: splunk lookup search two columns csv

ITWhisperer — Tue, 18 Jul 2023 08:12:19 GMT

| lookup email_users.csv address AS SenderAddress

Re: splunk lookup search two columns csv

sulaimancds — Tue, 18 Jul 2023 08:20:37 GMT

hi i am trying to get date field in the results , i cannot get it . results are showing but i need the date from the csv

the event date is from the email_users.csv

Re: splunk lookup search two columns csv

ITWhisperer — Tue, 18 Jul 2023 08:27:39 GMT

Your csv has email addresses and dates.

What are you looking up in the csv? SenderAddress or Recipient?

Re: splunk lookup search two columns csv

sulaimancds — Tue, 18 Jul 2023 08:36:24 GMT

both address and event date

address	event_date
123@abc.com	2/10/2023

so after the query is run , against address , if there is result , show the date as well.

Re: splunk lookup search two columns csv

ITWhisperer — Tue, 18 Jul 2023 08:56:12 GMT

After the query has run, you have two addresses, which do you want to look up the date for?

Re: splunk lookup search two columns csv

sulaimancds — Wed, 19 Jul 2023 10:34:59 GMT

the query is working now to search from csv column address , but event date column should also be shown

Re: splunk lookup search two columns csv

ITWhisperer — Wed, 19 Jul 2023 11:08:25 GMT

If you are not prepared to answer the question(s) to clarify your requirement, how can you expect us to provide you with a solution?

Re: splunk lookup search two columns csv

sulaimancds — Mon, 24 Jul 2023 03:16:47 GMT

Hii,

I have answered your queries , can you please help.

Re: splunk lookup search two columns csv

ITWhisperer — Mon, 24 Jul 2023 07:06:46 GMT

Which address field from your current result do you want to look up the date for from your lookup file?

Re: splunk lookup search two columns csv

sulaimancds — Wed, 02 Aug 2023 05:34:57 GMT

Sender Address , then the event date from csv will be shown in the results as well

Re: splunk lookup search two columns csv

ITWhisperer — Wed, 02 Aug 2023 05:39:18 GMT

index=mail [ | inputlookup email_users.csv | rename address AS query | fields query ] | dedup MessageTraceId | lookup email_domain_whitelist domain AS RecipientDomain output domain as domain_match | where isnull(domain_match) | lookup all_email_provider_domains domain AS RecipientDomain output domain as domain_match2 | where isnotnull(domain_match2) | stats values(RecipientAddress) as Recipient values(Subject) as Subject earliest(_time) AS "Earliest" latest(_time) AS "Latest" values(Status) as Status by RecipientDomain SenderAddress | eval subject_count=mvcount(Subject) | sort - subject_count | convert ctime("Latest") | convert ctime("Earliest") | lookup email_users.csv address AS SenderAddress

(As I suggested 2 weeks ago)

Re: splunk lookup search two columns csv

sulaimancds — Wed, 02 Aug 2023 05:44:35 GMT

Error in 'lookup' command: All of the fields in the lookup table are specified as lookups, leaving no destination fields.

so when there are results , the SenderAdress should lookup at the csv again and output another column call event date.

Re: splunk lookup search two columns csv

ITWhisperer — Wed, 02 Aug 2023 06:20:29 GMT

I am not sure which lookup is failing as you haven't shown the fields from all the lookups.

For the second part, you could try this (although there doesn't appear to be a date field in the results at the moment so it shouldn't be a problem).

| lookup email_users.csv address AS SenderAddress OUTPUT date as EventDate