Splunk Search
Highlighted

What is the best way to compare emails in two different formats

Builder

I have two fields, I need to compare, that contain an email address, but in different format:
Format 1) firstname.lastname@domain.com
Format 2) firstnamelastnamedomain_com

What's the best way to compare those fields?

Thank you in advance

0 Karma
Highlighted

Re: What is the best way to compare emails in two different formats

Motivator

you could always convert one to the other format then directly compare them using regular expressions
or break up the address into fields (firstname, lastname, domain, etc) and compare each piece directly.

0 Karma
Highlighted

Re: What is the best way to compare emails in two different formats

Personally, I'd convert the events with the first format to match the second format and then compare them. It would be something like this run anywhere command:
| makeresults| eval first="first.last@test.com", second="first_last_test_com" | eval compare_first=first | rex mode=sed field=compare_first "s/[\.@]/_/g" | where compare_first=second

And if you didn't need to preserve the first one, you could make it even easier:
| makeresults| eval first="first.last@test.com", second="first_last_test_com" | rex mode=sed field=first "s/[\.@]/_/g" | where first=second

View solution in original post

Highlighted

Re: What is the best way to compare emails in two different formats

Builder

Thank you @elliotproebstel for your suggestions. Will try to use that!
I've converted the regular formatted email "firstname.lastname@domain.com" to the second format by
replacing "." and "@" with "_":

Field "userid" had value firstname.lastname@domain.com.

...|eval useridformated = replace(userid,"\.","_") | eval useridformated = replace(useridformated,"@","_") ...
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.