topic Re: splunk query only show 1 recipent in Alerting

Why does splunk query only show 1 recipent?

sulaimancds — Mon, 27 Feb 2023 14:33:19 GMT

index=mail sender!="postmaster@groupncs.onmicrosoft.com" | 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) values(subject) earliest(_time) AS "Earliest" latest(_time) AS "Latest" count by RecipientDomain sender | sort -count | convert ctime("Latest") | convert ctime("Earliest")

original command above

modify command below

when i use where, there is no results showing. i only want to show results of a single recipient. if there are many do not show it .

Re: splunk query only show 1 recipent

sulaimancds — Fri, 24 Feb 2023 03:14:07 GMT

please help

Re: splunk query only show 1 recipent

scelikok — Fri, 24 Feb 2023 03:24:51 GMT

Hi @sulaimancds,

You can use mvcount() function to count recipients like below;

index=mail sender!="postmaster@groupncs.onmicrosoft.com" | 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 values(subject) as subject earliest(_time) AS "Earliest" latest(_time) AS "Latest" count by RecipientDomain sender | where mvcount(recipient)=1 | sort -count | convert ctime("Latest") | convert ctime("Earliest")

Re: splunk query only show 1 recipent

sulaimancds — Fri, 24 Feb 2023 03:28:55 GMT

what about this command ? only datetime is not showing. only show results that contains 1 recipient.

Re: splunk query only show 1 recipent

scelikok — Fri, 24 Feb 2023 03:46:25 GMT

I didn't understand your question. Is your problem only for missing the DateTime field? That search does not give any output.

Did you try my reply? It should show you what you want.

Please be more specific.

Re: splunk query only show 1 recipent

sulaimancds — Fri, 24 Feb 2023 03:46:59 GMT

please try for the other command

Re: splunk query only show 1 recipent

sulaimancds — Fri, 24 Feb 2023 04:10:08 GMT

okay sorry. for the new command which you have given , i would like to count the amount of subjects ?

for example in 24 hours , 10 emails , were being sent , so subject count should be 10 ? can you modify it for me ?

i will discuss the other command later

Re: splunk query only show 1 recipent

scelikok — Fri, 24 Feb 2023 04:15:23 GMT

Please try below for subject count, it will show you the unique subject count.

index=mail sender!="postmaster@groupncs.onmicrosoft.com" | 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 values(subject) as subject earliest(_time) AS "Earliest" latest(_time) AS "Latest" count by RecipientDomain sender | where mvcount(recipient)=1 | eval subject_count=mvcount(subject) | sort -count | convert ctime("Latest") | convert ctime("Earliest")

Re: splunk query only show 1 recipent new query

sulaimancds — Fri, 24 Feb 2023 04:18:33 GMT

this is my current modified command, i want to show only 1 recipient, but sort recipient == 1 does not work.

also i would like to show subject count , from this sender to this recipent , in last 24 hours , 10 emails was sent so 10 subject count.

In addition to that , ctime(DateTime) does not work.

kindly help.

Re: splunk query only show 1 recipent new query

scelikok — Fri, 24 Feb 2023 04:26:12 GMT

Please try below;

index=mail sender!="postmaster@groupncs.onmicrosoft.com" | 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 first(_time) as firstTime latest(_time) as lastTime values(subject) as subject values(recipient) as recipient by sender | where mvcount(recipient)=1 | eval subject_count=mvcount(subject) | convert ctime(*Time)

Re: splunk query only show 1 recipent

sulaimancds — Fri, 24 Feb 2023 07:28:26 GMT

hi i do not need the count

count by RecipientDomain sender

can remove this option and give me the full command without count
also , i want to sort subject count from high to low , 10 highest being on top and low 1 being below.

Re: splunk query only show 1 recipent

scelikok — Fri, 24 Feb 2023 08:04:10 GMT

Hi, please try below;

index=mail sender!="postmaster@groupncs.onmicrosoft.com" | 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 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 | convert ctime("Latest") | convert ctime("Earliest")

Re: splunk query only show 1 recipent

sulaimancds — Fri, 24 Feb 2023 09:02:40 GMT

Thank you, it works ,

next i would like to include in the results if there are any attachments in the email, show me the attachment name and size of the attachment in MB/GB.

Is this possible ?

Adding on ,

also i have list of suspicious keywords to in a list in lookup editor called suspicoussubject_keywords.

can you include the query to lookup for this keyword in subject and then display results?

Re: splunk query only show 1 recipent

sulaimancds — Mon, 27 Feb 2023 01:16:22 GMT

also i have list of suspicious keywords to in a list in lookup editor called suspicoussubject_keywords.

can you include the query to lookup for this keyword in subject and then display results?

for example if there is the worder tender , in subject , results will be displayed.

Re: splunk query only show 1 recipent

sulaimancds — Mon, 27 Feb 2023 04:35:55 GMT

hi>