topic Re: email filter query in Alerting

Search to filter email?

sulaimancds — Mon, 13 Mar 2023 13:47:39 GMT

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

Re: email filter query

gcusello — Mon, 13 Mar 2023 06:39:46 GMT

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

Re: email filter query

sulaimancds — Mon, 13 Mar 2023 06:43:48 GMT

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.

Re: email filter query

gcusello — Mon, 13 Mar 2023 06:55:45 GMT

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

Re: email filter query

sulaimancds — Mon, 13 Mar 2023 06:58:43 GMT

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.

Re: email filter query

sulaimancds — Mon, 13 Mar 2023 07:01:11 GMT

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

Re: email filter query

gcusello — Mon, 13 Mar 2023 07:02:39 GMT

Hi @sulaimancds,

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

Ciao.

Giuseppe

Re: email filter query

sulaimancds — Mon, 13 Mar 2023 07:06:08 GMT

it does not work

this work , without any filter.

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

Re: email filter query

gcusello — Mon, 13 Mar 2023 07:09:01 GMT

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

Re: email filter query

sulaimancds — Mon, 13 Mar 2023 07:11:07 GMT

| 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.

Re: email filter query

gcusello — Mon, 13 Mar 2023 07:14:27 GMT

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

Re: email filter query

sulaimancds — Mon, 13 Mar 2023 07:20:20 GMT

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.

Re: email filter query

gcusello — Mon, 13 Mar 2023 07:24:11 GMT

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

Re: email filter query

sulaimancds — Mon, 13 Mar 2023 07:32:58 GMT

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.

Re: email filter query

gcusello — Mon, 13 Mar 2023 07:37:54 GMT

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

Re: email filter query

sulaimancds — Mon, 13 Mar 2023 07:40:19 GMT

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

Re: email filter query

gcusello — Mon, 13 Mar 2023 07:42:51 GMT

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