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

[sulaimancds]

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")

Hi

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.

[ITWhisperer]

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

[sulaimancds]

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

[ITWhisperer]

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

[sulaimancds]

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

[ITWhisperer]

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

[sulaimancds]

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

[ITWhisperer]

| lookup email_users.csv address AS SenderAddress

[sulaimancds]

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 event date AS date

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

[ITWhisperer]

Your csv has email addresses and dates.

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

[sulaimancds]

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.

[ITWhisperer]

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

[sulaimancds]

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

[ITWhisperer]

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?

[sulaimancds]

Hii,

I have answered your queries , can you please help.

[ITWhisperer]

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

[sulaimancds]

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

[ITWhisperer]

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)

[sulaimancds]

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.

[ITWhisperer]

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