Using regex to extract domain from delimited email recipients, and then count them

[jamesklassen]

I need to count the number of incoming emails from external and internal sources, and the number going out to internal and external sources.

My data has sender_address and recipient_address fields. They look like: "email1@domain.com;email2@domain2.com"

I have used regex to extract the domain but I am having trouble extracting the domain for additional email addresses when specified (delimited by 😉

index=hubtracking | rex field=sender_address ".[^@]+?@(?<sender_domain>.+)" | rex field=recipient_address ".[^@]+?@(?<recipient_domain>.+)" | stats count as TotalMessages, count(eval(sender_domain="mydomain.ca" AND recipient_domain="mydomain.ca")) as FromInternal, (eval(sender_domain!="mydomain.ca" AND recipient_domain="mydomain.ca")) as FromExternal

This kind of works...but the problem is that I'm only counting the first address specified in that field. I imagine that I first need to extract only the domain names, and then count them?

[jamesklassen]

Got it, here's an example for anyone else working to get stats from Exchange 2010 message tracking logs:

index=hubtracking NOT SystemMailbox* | dedup message_id | rex field=sender_address "[a-zA-Z][\w\.-]*[a-zA-Z0-9]@(?<sender_domain>[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z])" | rex field=recipient_address "[a-zA-Z][\w\.-]*[a-zA-Z0-9]@(?<recipient_domain>[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z])" | stats sum(recipient_count) as TotalMessages, count(eval(sender_domain="yourdomain.com" AND recipient_domain="yourdomain.com")) as FromInternal

[jamesklassen]

I have the regex now, I just need to figure out how to count all of the domains now: rex field=sender_address "[a-zA-Z][\w.-][a-zA-Z0-9]@(?[a-zA-Z0-9][\w.-][a-zA-Z0-9].[a-zA-Z][a-zA-Z.]*[a-zA-Z])" | where(name = ";")