Search to filter email?

[sulaimancds]

index=mail
| dedup MessageTraceId
| dedup MessageId
| dedup subject
| 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)
| table RecipientDomain SenderAddress RecipientAddress Subject Received

hi this 3 lines are not working for this query. Please help.
| where mvcountRecipientAddress=1
| eval subject_count=mvcount(Subject)
| sort - subject_count

[gcusello]

Hi @sulaimancds,

at first are you sure that the three dedups will correctly work?

are you sure that you have the correct results or that it's better to dedup for the three fields in one command?

Anyway, where do the fields "mvcountRecipientAddress" and "sunject" come from: the main search or the lookup? I don't see them in lookup, are you sure that they are present.

Then where do you put the three not working rows in your search?

Ciao.

Giuseppe

[sulaimancds]

can dedup all in a single line.

subject is there.

mvcount is there.

this is my old command.

| stats values(recipient) as recipient values(subject) as subject earliest(_time) AS "Earliest" latest(_time) AS "Latest" by RecipientDomain sender
| where mvcount(recipient)=1
| eval subject_count=mvcount(subject)
| sort - subject_count

i need to move this into my new command , which i first posted.

[gcusello]

Hi @sulaimancds ,

please try this:

<your previous rows>
| stats 
   values(recipient) AS recipient 
   dc(recipient) AS recipient_count
   values(subject) AS subject 
   dc(subject) AS subject_count
   earliest(_time) AS "Earliest" 
   latest(_time) AS "Latest" 
   BY RecipientDomain sender
| where recipient_count=1
| sort -subject_count

Ciao.

Giuseppe

[sulaimancds]

hi

index=mail
| dedup Subject
| 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(recipient) AS recipient
dc(recipient) AS recipient_count
values(subject) AS subject
dc(subject) AS subject_count
earliest(_time) AS "Earliest"
latest(_time) AS "Latest"
BY RecipientDomain sender
| where recipient_count=1
| sort -subject_count

i cannot see anything under statitics.

[sulaimancds]

in events i can see, i cannot see anything under statistics.

[gcusello]

Hi @sulaimancds,

what about if you remove the condition "| where recipient_count=1"?

Ciao.

Giuseppe

[sulaimancds]

it does not work

index=mail
| dedup MessageTraceId
| dedup MessageId
| dedup subject
| 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)
| table RecipientDomain SenderAddress RecipientAddress Subject Received

this work , without any filter.

stats does not work only table works like this without any filters. Please help.

[gcusello]

Hi @sulaimancds,

continue debugging removing the other conditions:

before "| where isnotnull(domain_match2)"

then "| where isnull(domain_match)"

to identify where is the issue

Ciao.

Giuseppe

[sulaimancds]

| table RecipientDomain SenderAddress RecipientAddress Subject Received

this work , without any filter.

stats does not work only table works like this without any filters. Please help.

i try to deubg it is not showing anything under statitics.

[gcusello]

Hi @sulaimancds,

if table works and stats doesn't work, it should mean that you haven't in any event both the fields used as keys in the stats command ("RecipientDomain" and "sender"), check if you have the 100% of these fields and if there are events where they are both present.

If there aren't you have to find a different aggregation logic.

Ciao.

Giuseppe

[sulaimancds]

in events SenderAddress is sender, in raw log

Recipient Domain is under INTERESTING FIELDS, which is working when using table command.

Please help for the last 3 lines.

[gcusello]

Hi @sulaimancds,

Yes they are, but tey are in the 100% of events?
probably the problem is that they aren't both present in events, so if you use "stats BY RecipientDomain sender" you haven't results

you could try to put

| fillnull value="-" RecipientDomain 
| fillnull value="-" sender

before the stats command, to be sure to have values in both the fields in each event.

Ciao.

Giuseppe

[sulaimancds]

i have able to make it work

index=mail
| dedup Subject
| 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 latest(_time) as "Time" by RecipientDomain SenderAddress
| where mvcount(Recipient)=1
| eval subject_count=mvcount(Subject)
| sort - subject_count
| convert ctime("Time")

please check.

[gcusello]

Hi @sulaimancds,

if it runs it's good for you and I'm happy for you!

Please make only one check:

the condition "| where mvcount(Recipient)=1" is always satisfied by definition, but you're sure that in Recipent you have only one value?

Ciao.

Giuseppe

[sulaimancds]

yes i only want to see 1 recipient , if there are 2 recipient i do not want the results to be displayed,

[gcusello]

Hi @sulaimancds,

I understood your requirement, but my question is: check if in recipient you effectively have one recipient and not two or more in the same field.

If it's true, you solved your issue.

Ciao.

Giuseppe