Kusto query for email filtering with regex

Question

Raw string:

| Mail_To_s                                        |
| "AAA@xxx.com,BBB@yyy.com,CCC@zzz.com,DDD@kkk.com"|

Current query:

CUSTOM_LOG_TABLE
| extend mail_count = countof(Mail_To_s, @"@", "regex")
| extend internal_mail_count = countof(tolower(Mail_To_s), @"yyy.com|zzz.com", "regex")
| extend external_mail_conut=mail_count - internal_mail_count
| where external_mail_count > 0

Results:

| mail_cout | internal_mail_count | external_mail_count | Mail_To_s                                        |
|     4     |          2          |           2         | "AAA@xxx.com,BBB@yyy.com,CCC@zzz.com,DDD@kkk.com"|

Expect results:

How to extend column "external_email" for email filtering with regex

| mail_cout | internal_mail_count | external_mail_count | external_mail                 | Mail_To_s                                       |                         
|     4     |          2          |           2         | ["AAA@xxx.com","DDD@kkk.com"] |"AAA@xxx.com,BBB@yyy.com,CCC@zzz.com,DDD@kkk.com"|

Answer 1

Here is one approach:

datatable(Mail_To_s:string)["AAA@xxx.com,BBB@yyy.com,CCC@zzz.com,DDD@kkk.com"]
| extend mail_count = countof(Mail_To_s, @"@", "regex")
| extend internal_mail_count = countof(tolower(Mail_To_s), @"yyy.com|zzz.com", "regex")
| extend external_mail_count=mail_count - internal_mail_count
| where external_mail_count > 0
| extend external_mail   = split(Mail_To_s,",")
| mv-apply external_mail   on (
     parse external_mail   with * "@" Domain
     | where Domain !in("yyy.com", "zzz.com")
     | summarize external_mail= make_set(external_mail)
)
| project-reorder mail_count, internal_mail_count, external_mail_count,external_mail

mail_count	internal_mail_count	external_mail_count	external_mail	Mail_To_s
4	2	2	[ "AAA@xxx.com", "DDD@kkk.com" ]	AAA@xxx.com,BBB@yyy.com,CCC@zzz.com,DDD@kkk.com