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.
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
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
Which date do you mean? Your stats already has earliest and latest
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 |
Your search returns these columns: Recipient, Subject, Earliest, Latest, Status, RecipientDomain, SenderAddress and subject_count - which of these is the event_date?
i want to include event date as well, it is from the csv , please help me for that
| lookup email_users.csv address AS SenderAddress
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
Your csv has email addresses and dates.
What are you looking up in the csv? SenderAddress or Recipient?
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.
After the query has run, you have two addresses, which do you want to look up the date for?
the query is working now to search from csv column address , but event date column should also be shown
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?
Hii,
I have answered your queries , can you please help.
Which address field from your current result do you want to look up the date for from your lookup file?
Sender Address , then the event date from csv will be shown in the results as well
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)
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.
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