csvfile search in values

[shrinivaskittur]

Hi,

I have csv file containing emailID and domain  and I would like to search the email exchanges between these two(emaild and domain)

Csv file looks like below 

emailID                                           domain

test1@company.com             abc.com

test2@company.com             xyz.com

test3@company.com             some.com

so on ..........

based on the above I need to check how many time the emails exchanged between emailID and domain, I tried with below query but unable to get the result

my search.... [| inputlookup test.csv | eval emailID = mvjoin(emailID ,",") | eval domain= "*@.".domain
| eval condition1 = "Sender IN (".domain.") AND Rcpt IN (".emailID .") " | return $condition1 ] | table Sender Rcpt

[richgalloway]

The current query looks for two literal strings in the Sender and Rcpt fields, which explains why you don't get the expected results.  See if this helps.

your search.... [| inputlookup test.csv 
  | eval domain= "*@.".domain
  | fields email domain | rename email as Sender, domain as Rcpt | format ] 
| table Sender Rcpt

[shrinivaskittur]

Hi,

Please help me to get the correct query for my search.

[shrinivaskittur]

Thank you for your reply, the suggested query is not giving me any outputs. If I select any one field I get one side result but when I select both fields "| fields email domain" then I won't get any result.

I want to achieve if any "EmailID" (listed in CSV) sends an email to any of the "domain"(listed in CSV) and vice versa should be shown in the search result.

[ITWhisperer]

your search.... [| inputlookup test.csv 
  | eval domain= "*@.".domain
  | fields domain | rename domain as Rcpt | format ] 
  [| inputlookup test.csv 
  | fields email | rename email as Sender | format ] 
| table Sender Rcpt

[shrinivaskittur]

No Output from this query 

[ITWhisperer]

Sorry, there was a typo - try this

your search.... [| inputlookup test.csv 
  | eval domain= "*@".domain
  | fields domain | rename domain as Rcpt | format ] 
  [| inputlookup test.csv 
  | fields email | rename email as Sender | format ] 
| table Sender Rcpt

[shrinivaskittur]

Hi,

Still the same, result is blank.

[ITWhisperer]

Perhaps there is a mismatch between your indexed data and your csv file, for example, space padding, case, etc. Have you tried using one of the values from the csv to see if you get any results

your search ... domain="*@abc.com"

[shrinivaskittur]

Hi,

I have already did this testing, I have taken sender and recipient from the recent logs and did the search using the same query but still not getting the result.

As said, I need both fields from csv  to be matched in search (sender and recipient) for example.

if sender A sends email to recipient B and also if recipient B replies emails to sender B, in both case I should get the result . sender A & B are in csv should match.

[ITWhisperer]

Can you share your full search and some anonymised sample events?