Splunk Search

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

cdson
Explorer

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:

Screen Shot 2022-11-09 at 11.06.14 AM.png

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

Labels (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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"
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...