topic Using regex to extract domain from delimited email recipients, and then count them in Splunk Search

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

jamesklassen — Thu, 24 Mar 2011 00:30:33 GMT

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?

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

jamesklassen — Mon, 28 Sep 2020 09:26:36 GMT

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

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

jamesklassen — Thu, 24 Mar 2011 03:54:23 GMT

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