topic Re: Search and compare data within 3 fields to find positive and negative matches in Splunk Search

Search and compare data within 3 fields to find positive and negative matches

Glioblaster — Wed, 29 Jul 2020 21:10:10 GMT

I have a search yielding data from three different email fields, call them msg.header.to{}, msg.header.cc{} and orig_recipient. I am looking to see if the email address contained within orig_recipient matches either of the other two. The issue is that Splunk captures the data differently in the msg.header columns.

For example, the msg.header columns output is "Smith, Joe <joe.smith@email.com>", while the output in the orig_recipient would only be "joe.smith@email.com". So, when I ask Splunk to tell me if the orig_recipient email address is in the msg.header.to{}, I get a negative. I have tried Like, If, Where and others, along with using wildcards but maybe my syntax is wrong.

I am looking to see how I can search within a field using the value of another field as the search parameter. Also, if that is not possible, extracting the data between the <> and putting it into another field to compare off of that field might work.

Thank you for your time and attention to this matter.

Re: Search and compare data within 3 fields to find positive and negative matches

to4kawa — Wed, 29 Jul 2020 21:48:10 GMT

|eval check=if(match('msg.header.to{}', orig_recipient), 1,0)

How about match()?

Re: Search and compare data within 3 fields to find positive and negative matches

bowesmana — Wed, 29 Jul 2020 22:00:27 GMT

@Glioblaster As well as using match, as suggested by @to4kawa you can always extract the email address using the rex statement, as in this example. Note that it still uses match() to do the compare.

| makeresults | eval Message="{\"message\":{\"hdr\":{\"to\":\"Smith, Joe <joe.smith@email.com>\",\"cc\":\"Smith, Fred <fred.smith@email.com>\"}},\"orig_recipient\":\"joe.smith@email.com\"" | spath input=Message | rex field=message.hdr.cc "<(?<email_cc>[^>]*)" | rex field=message.hdr.to "<(?<email_to>[^>]*)" | eval to_is_orig=if(match(orig_recipient,email_to),"EQUAL","NOT_EQUAL")

Hope this is useful.

Re: Search and compare data within 3 fields to find positive and negative matches

Glioblaster — Thu, 30 Jul 2020 11:00:17 GMT

Thank you but the result was all negative as it was the same problem I am running into where Splunk does not look within the field to match the results so the email addresses inside the <> are not being parsed against the email addresses in orig_recipient.

Re: Search and compare data within 3 fields to find positive and negative matches

Glioblaster — Thu, 30 Jul 2020 11:33:34 GMT

Thank you. I tried this but it did not like my following syntax (received "Error in 'eval' command. The expression is malformed.") as I need to search against the fields, I substituted the field names where you put the email addresses. I wrote the following:

eval Message="{\"message\":{\"hdr"\{\"to"\:\"'msg.header.to{}'\",\"cc\":\"'msg.header.cc{}'\"}},\"orig_recipient\""

All else was the same from your reply. In addition, I will be putting an OR command as I need to search against the .to and .cc fields.

Re: Search and compare data within 3 fields to find positive and negative matches

Glioblaster — Thu, 30 Jul 2020 19:24:37 GMT

Update, so as I am continuing to work on this I changed the parameters to this:

rex field=msg.header.to{} "<(?<test>.*)>" and it worked with providing me the email address contained within the "<>". My next issue is to make it work on multiple email addresses within the same field. Suggestions are welcomed. After I get the email addresses extracted out into a new field, I can then write comparison expressions against my orig_recipient field.

Re: Search and compare data within 3 fields to find positive and negative matches

Glioblaster — Thu, 30 Jul 2020 20:30:24 GMT

Solved:

This is what worked for my search and I also had some mixed case letters so I added the case "lower".

| rex field=msg.header.to{} max_match=0 "<(?<test>.*)>"

| rex field=msg.header.cc{} max_match=0 "<(?<test2>.*)>"

| eval test=lower(test)

| eval test=lower(test2)

| eval test3=if(orig_recipient=test OR orig_recipient=test2), "TRUE", "FALSE")