Splunk Search

Match partial value of 2 fields

geraldcontreras
Path Finder

Hi All,

I have a dashboard that accepts user input for a username to search emails.
Im trying to display Recipients address that contain the username as a partial match.

EG- the value of SenderAddress will match on RecipientAddress:
SenderAddress=John.doe
will match:
RecipientAddress=doefamily@gmail.com
RecipientAddress=j.doe@gmail.com
RecipientAddress=family@doe.net

I tried via regex to extract the first and lastname fields to use for matching, using eval and match but i cant get it to work.

index=azure sourcetype=ms:o365:reporting:messagetrace SenderAddress=john.doe@mycompany.com
| rex field=SenderAddress "(?<user_wild>[^\.]+).(?<user_wild2>.*?)\@"
| eval results=if(like(RecipientAddress,user_wild2),"Yes","No")
| table results user_wild user_wild2 RecipientAddress

I have also tried

index=azure sourcetype=ms:o365:reporting:messagetrace SenderAddress=john.doe@mycompany.com
| rex field=SenderAddress "(?<user_wild>[^\.]+).(?<user_wild2>.*?)\@"
| eval results2=if(like("%".(RecipientAddress)."%","%".(user_wild2)."%"),"Yes","No")
| table results user_wild user_wild2 RecipientAddress

The regex works but it wont match, for example the table results are:

results user_wild user_wild2 RecipientAddress
No John Doe doek@gmail.com

Thank you in advance

0 Karma
1 Solution

geraldcontreras
Path Finder

Thanks for all your help Giuseppe, i much appreciate it 😄

Yes the fields are correct, they are standard from the Microsoft TA.

I did some playing around with it yesterday with some wider sets of data, it appears that the match works, but only where the regex field is all lowercase. i was able to match on other email addresses where there were no capitals in the data. So my search and your searches all work, just not where there is a capital in the regex field.

So
john.doe@mycompany.com
will match
doek@gmail.com
but
John.Doe@mycompany.com
will not match
doek@gmail.com

Looks like something to do with the capitals ( i thought it would match regardless of case, who knew!).
I did show the fact it was displaying capitals in my comments but i guess this flew over both our heads because the field values in the search arent case sensitive, but that doesn't mean the tabled results will case match the base search!

So the final solution is to make it lower case because the RecipientAddress is always lowercase in our data:

Final simplified working query:

  index=azure sourcetype=ms:o365:reporting:messagetrace SenderAddress=john.doe@mycompany.com
 | rex field=SenderAddress "\.(?.*?)\@"
 | eval user_wild=lower(user_wild)
  | eval results=if(like(RecipientAddress,"%".user_wild."%"),"Yes","No") 
  | table results user_wild RecipientAddress

View solution in original post

0 Karma

geraldcontreras
Path Finder

Thanks for all your help Giuseppe, i much appreciate it 😄

Yes the fields are correct, they are standard from the Microsoft TA.

I did some playing around with it yesterday with some wider sets of data, it appears that the match works, but only where the regex field is all lowercase. i was able to match on other email addresses where there were no capitals in the data. So my search and your searches all work, just not where there is a capital in the regex field.

So
john.doe@mycompany.com
will match
doek@gmail.com
but
John.Doe@mycompany.com
will not match
doek@gmail.com

Looks like something to do with the capitals ( i thought it would match regardless of case, who knew!).
I did show the fact it was displaying capitals in my comments but i guess this flew over both our heads because the field values in the search arent case sensitive, but that doesn't mean the tabled results will case match the base search!

So the final solution is to make it lower case because the RecipientAddress is always lowercase in our data:

Final simplified working query:

  index=azure sourcetype=ms:o365:reporting:messagetrace SenderAddress=john.doe@mycompany.com
 | rex field=SenderAddress "\.(?.*?)\@"
 | eval user_wild=lower(user_wild)
  | eval results=if(like(RecipientAddress,"%".user_wild."%"),"Yes","No") 
  | table results user_wild RecipientAddress
0 Karma

gcusello
SplunkTrust
SplunkTrust

HI geraldcontreras,
there's an error in your regex: "dot" is a special char that must be always escaped!
so use this regex

^(?<user_wild>[^\.]+)\.(?<user_wild2>[^\@]*)

You can test it at https://regex101.com/r/8dPwxU/1 .

Then, if you use like and want to search as a substring, you have to use "%" instead "*".
So try something like this:

| makeresults
| eval SenderAddress="John.doe", RecipientAddress="doefamily@gmail.com"
| rex field=SenderAddress "^(?<user_wild>[^\.]+)\.(?<user_wild2>[^\@]*)"
| eval user_wild2="%".user_wild2."%"
| eval results=if(like(RecipientAddress,user_wild2),"Yes","No")
| rex field=user_wild2 "\%(?<user_wild2>[^\%]*)"
| table results user_wild user_wild2 RecipientAddress

Ciao.
Giuseppe

0 Karma

geraldcontreras
Path Finder

Hi Giuseppe,

Thanks for that.
The regex works even without escaping the "dot" because it tables as i expect (dot just matches "any character"). But either way your regex is better/more efficient.

On that note, the problem is i need it to match against all results. The regex isnt where the issue lies, and forgive me if im not explaining what i need very well.

|makeresults works if i specifically put the recipient AND sender address, but the point of my search is that i dont know what the recipient address will be.

Essentially, i need to make it work where:
1. i enter the SenderAddress,
2. regex pulls apart the first part of the sender address to give a "first and last name"
3. i then can search for any match of the last name being found in all results of RecipientAddress because i do not know the values of RecipientAddress and there could be hundreds so i can use makeresults for each one.

basically, i am looking for users sending to their non company personal email addresses, filtering for where their non-company account contains part of their name.
EG- john.doe@microsoft.com sending to doe@gmail.com
- i dont want to see any other emails john.doe@microsoft.com sends to unless the recipient contains "doe" somewhere in the email.

Basically i need something like the following, because ultimatley this is in a dashboard where a user inputs the SenderAddress as a token value.

Something like this but working?
index=azure sourcetype=ms:o365:reporting:messagetrace

| search
[| makeresults
| SenderAddress=john.doe
| rex field=SenderAddress "^(?[^.]+).(?[^\@])"
| eval user_wild2="%".user_wild2."%"
| eval results=if(like(RecipientAddress,user_wild2),"Yes","No")
| rex field=user_wild2 "\%(?[^\%]
)"
| table results user_wild user_wild2 RecipientAddress ]
| table results user_wild user_wild2 RecipientAddress

0 Karma

gcusello
SplunkTrust
SplunkTrust

HI geraldcontreras,
sorry I was not clear: makeresults is a way to show an example and to make a test without your data, you don't need it!

Try something like this:

index=azure sourcetype=ms:o365:reporting:messagetrace
| rex field=SenderAddress "^(?[^.]+).(?[^\@])" 
| eval user_wild2="%".user_wild2."%" 
| eval results=if(like(RecipientAddress,user_wild2),"Yes","No") 
| rex field=user_wild2 "\%(?[^\%])" 
| table results user_wild user_wild2 RecipientAddress

In addition, you don't need to put a subsearch in a dedicated search command, you can put it in the main search.

Ciao.
Giuseppe

0 Karma

geraldcontreras
Path Finder

Hi Giuseppe,

Im still getting the same results whether its using your regex or my far less efficient regex- both give the same results. The rex fields extracted but the "like" in the eval not matching.

The initial query you last gave throws the following error:
"Error in 'rex' command: Encountered the following error while compiling the regex '^(?[^.]+).(?[^\@])': Regex: unrecognized character after (? or (?-"

so i changed it to the following

 index=azure sourcetype=ms:o365:reporting:messagetrace SenderAddress=john.doe@mycompany.com
| rex field=SenderAddress "^(?<user_wild>[^\.]+)\.(?<user_wild2>[^\@]*)"
 | eval user_wild2="%".user_wild2."%" 
 | eval results=if(like(RecipientAddress,user_wild2),"Yes","No") 
| rex field=user_wild2 "\%(?<user_wild2>[^\%]*)"
 | table results user_wild user_wild2 RecipientAddress

Which fails to match (but still tabling the rex field as expected), giving:

results user_wild RecipientAddress
No doe doek@gmail.com

I have tried multiple different versions of your suggestions. With both your SPL and my original ones in my first post, i can get the values of user_wild2 to table as "doe".... but the eval using "like" never matches. This is where the issue is occurring.

i tried also simplifying it with the below, but it still doesn't match.. it displays the correct extraction via rex, but wont match.

index=azure sourcetype=ms:o365:reporting:messagetrace SenderAddress=john.doe@mycompany.com
| rex field=SenderAddress ".(?.*?)\@"
| eval results=if(like(RecipientAddress,"%".user_wild."%"),"Yes","No")
| table results user_wild RecipientAddress

Tabled results:
results user_wild RecipientAddress
No doe doek@gmail.com

The only way it works is using the

| makeresults
 | eval SenderAddress="john.doe", RecipientAddress="doek@bigpond.net.au"
 | rex field=SenderAddress "^(?<user_wild>[^\.]+)\.(?<user_wild2>[^\@]*)"
 | eval user_wild2="%".user_wild2."%"
 | eval results=if(like(RecipientAddress,user_wild2),"Yes","No")
 | rex field=user_wild2 "\%(?<user_wild2>[^\%]*)"
 | table results user_wild user_wild2 RecipientAddress

The exact same search but with the top 2 lines excluded and replaced with base search does not work, it shows all the correct extractions but has a value of "No" for "results"

results user_wild RecipientAddress
No doe doek@gmail.com

Sorry, its a tricky one, i did exhaust a lot of splunk answers before posting. Not many people seem to be doing this with the field value against another field value. most people are specifying the value against the value of a field.

The sub search was just me trying to work in the "makeresults" in case that was something i was missing (makeresuls needing to be at the beginning of a search.... which ive never used so wasnt sure).
:)

0 Karma

gcusello
SplunkTrust
SplunkTrust

HI geraldcontreras,
sorry for the regex, there was a copy error!

Anyway, in your main search check the names of the fields you use (they are case sensitive): in other words run the main search index=azure sourcetype=ms:o365:reporting:messagetrace and see the names of the used fields: SenderAddress and RecipientAddress.
Then verify if in thje search they are correct, because it shoud run.

This is an example where I extract a part of a field (sourcetype) and I use this part (splunkd) to search in another field (source), in other words your use case:

index=_internal 
| head 1000
| rex field=sourcetype "^(?<proc>[^_]+)"
| eval proc="%".proc."%" 
| eval results=if(like(source,proc),"Yes","No") 
| rex field=proc "\%(?<proc>[^\%]*)"
| table results proc source

Ciao.
Giuseppe

0 Karma

geraldcontreras
Path Finder

Hi Giuseppe,

Thanks for all your help, i much appreciate it 😄

Yes the fields are correct, they are standard from the Microsoft TA.

I did some playing around with it yesterday with some wider sets of data, it appears that the match works, but only where the regex field is all lowercase. i was able to match on other email addresses where there were no capitals in the data. So my search and your searches all work, just not where there is a capital in the regex field.

So
john.doe@mycompany.com
will match
doek@gmail.com
but
John.Doe@mycompany.com
will not match
doek@gmail.com

Looks like something to do with the capitals ( i thought it would match regardless of case, who knew!).
I did show the fact it was displaying capitals in my comments but i guess this flew over both our heads because the field values in the search arent case sensitive, but that doesn't mean the tabled results will case match the base search!

So the final solution is to make it lower case because the RecipientAddress is always lowercase in our data:

Final simplified working query:

 index=azure sourcetype=ms:o365:reporting:messagetrace SenderAddress=john.doe@mycompany.com
| rex field=SenderAddress "\.(?.*?)\@"
| eval user_wild=lower(user_wild)
 | eval results=if(like(RecipientAddress,"%".user_wild."%"),"Yes","No") 
 | table results user_wild RecipientAddress
0 Karma

gcusello
SplunkTrust
SplunkTrust

HI geraldcontreras,
you approved your own answer, if I helped you, please accept and/or upvote my answer.

Ciao and see next time.
Giuseppe

0 Karma

geraldcontreras
Path Finder

Hi Giuseppe,

Yes i did accept my own answer as the answer because ultimately the only issue i had in the end was transforming the rex field to be lower case to match using
| eval user_wild=lower(user_wild)

And i believe that others searching for the same answer would do best to simply see the answer. Which is simply "like" matches are case sensitive.

My original search would have worked if i used lower. So all though every point you made was valid, they did not help to answer/resolve my specific problem.

Thank you very much for your effort and time though, i truly appreciate it.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...