Why does splunk query only show 1 recipent?

[sulaimancds]

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 

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)
| table sender recipient subject DateTime
| sort recipent == 1
| where recipient == 1
| convert ctime(DateTime)

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 .

[scelikok]

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")

[sulaimancds]

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.

[sulaimancds]

hi>

[sulaimancds]

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?

[scelikok · Accepted solution]

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")

[sulaimancds]

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.

[scelikok]

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.

[sulaimancds]

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)
| table sender recipient subject DateTime
| sort recipient == 1
| convert ctime(DateTime)

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.

[scelikok]

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)

[sulaimancds]

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

[scelikok]

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")

[sulaimancds]

please try for the other command 

[sulaimancds]

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)
| table sender recipient subject DateTime
| sort recipent == 1
| where recipient == 1
| convert ctime(DateTime)

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

[sulaimancds]

please help