How to use Wildcards with eval, stats, and count?

[cdson]

Hello! I have a field called "Customers Email" and I wanted to get a count of all the emails that end in .gov, .edu, .org and so on. I am using the eval and stats count functions to do this; however, my results show up with values of 0 for each type of email. Since wildcards do not work with eval, I put the wildcards like ".*..gov" so that it would just look at the .gov etc. of each email.

This is my search:

| stats count(eval("Customers Email" = ".*..gov")) as ".gov", count(eval("Customers Email" = ".*..org")) as ".org", count(eval("Customers Email" = ".*..com")) as ".com", count(eval("Customers Email" = ".*..edu")) as ".edu", count(eval("Customers Email" = ".*..us")) as ".us", count(eval("Customers Email" = ".*..net")) as ".net"

This is the output I get from running this search:

Is there a reason why I am getting a count of 0?

[ITWhisperer]

Field names with spaces in should be in single quotes (not double quotes) - you could use the match function, note that dots (.) are wild in regex so need to be escaped - try and avoid field names with dots in. Try this

| stats count(match('Customers Email',".*\.gov")) as "gov", count(match('Customers Email',".*\.org")) as "org", count(match('Customers Email',".*\.com")) as "com", count(match('Customers Email',".*\.edu")) as "edu", count(match('Customers Email',".*\.us")) as "us", count(match('Customers Email',".*\.net")) as "net"

[bowesmana]

You'll need to put the 'eval' statement in those stats commands suggested by @ITWhisperer 

| stats count(eval(match('Customers Email',".*\.gov"))) as "gov"
        count(eval(match('Customers Email',".*\.org"))) as "org"
        count(eval(match('Customers Email',".*\.com"))) as "com"
        count(eval(match('Customers Email',".*\.edu"))) as "edu"
        count(eval(match('Customers Email',".*\.us"))) as "us"
        count(eval(match('Customers Email',".*\.net"))) as "net"

but note that the match statement will match anywhere in the string, so if your email is

me@this.governor.com

it will match .gov as well as .com

so you should add a $ sign at the end of the match string to ensure the end of string.

Alternatively, if you are just looking to count TLDs in email addresses, you could do

| rex field="Customers Email" ".*\.(?<tld>.*)"
| stats count by tld
| transpose 0 header_field=tld
| fields - column

which would catch all tlds, not just the ones in the eval - just in case you had some there you didn't expect - if you don't want them, you can always filter them out.