Compare Different Fields in Different Events

[lgsplunks]

New to Splunk and banging my head against the wall with this problem for over a day now. Please help...

Need to compare two different fields from two different events to determine whether the values of those fields match.

I ran a search that returns events. All events have an ACCOUNT_NUM field. Depending on the event, it will have either a DATE_TYPE1 field or a DATE_TYPE2 field. The report should display each distinct ACCOUNT_NUM that has one of each DATE_TYPE - so, a column for ACCOUNT NUM, a column for DATE_TYPE1, a column for DATE_TYPE2, and a column for DATE_STATUS ("Match" or "No Match") to indicate whether the two dates match. 

So far, I have:

| stats values(DATE_TYPE1) AS "Date One" values(DATE_TYPE2) AS "Date Two" count by ACCOUNT_NUM
| where count > 1

This groups the distinct ACCOUNT_NUM and shows me the two DATE_TYPES but how do I indicate whether the two dates match? I tried adding:

| eval DATE_STATUS=if(DATE_TYPE1=DATE_TYPE2, "Match", "No Match")

but this returns "No Match" for all of the events. My understanding is this is because 

| eval

is evaluating each event individually. Since no event has both date types, it is not finding a match. How can I get it to compare the date types of each distinct account number as grouped together by my

| stats

command?

[bowesmana · Accepted solution]

Assuming you will have only 2 events for each ACCOUNT_NUM and the date fields will NOT be multivalue, then the problem in your search is that

| stats values(DATE_TYPE1) AS "Date One" values(DATE_TYPE2) AS "Date Two" count by ACCOUNT_NUM
| eval DATE_STATUS=if(DATE_TYPE1=DATE_TYPE2, "Match", "No Match")

cannot work, as your eval statement is not looking at the newly named fields - the fields are no longer DATE_TYPE1 and DATE_TYPE2, as you have used 'as XX' in the stats command to rename those fields as "Date One and "Date Two".

You should use the new field names in the test. Here's an example you can run that demonstrates this working

| makeresults count=90
| eval ACCOUNT_NUM=random() % 60
| eval DATE=strftime(_time - (random() % 86400), "%F")
| eval t=random() % 2 
| eval DATE_TYPE1=if(t == 0, DATE, null())
| eval DATE_TYPE2=if(t == 1, DATE, null())
``` above just sets up an example data set```
| stats values(DATE_TYPE1) AS "Date One" values(DATE_TYPE2) AS "Date Two" count by ACCOUNT_NUM
| where count == 2 AND mvcount('Date One')=1 AND mvcount('Date Two')=1
| eval DATE_STATUS=if('Date One'='Date Two', "Match", "No Match")

See the last line for what you should be using - NOTE: Use single quotes round the field names in the eval statement, not double quotes.

Note too the comments from @richgalloway about how to handle dates by treating them as epoch - good advice.

[lgsplunks]

Thank you! This worked. There should be only two events for each ACCOUNT_NUM as I did a dedup as part of the initial search (not included in my question). Performing the eval with the new field names AND the single quotes was the solution. I can't believe I missed the part about single quotes in my training, so many hours wasted... Thank you again!

[richgalloway]

The failure to match dates is not the fault of eval.  It's because Splunk cannot compare dates as strings.  It becomes more complicated if the date is in a multi-value field, but we'll assume that's not the case here.

To compare dates, convert them to epoch (integer) format using the strptime function.

| stats values(DATE_TYPE1) AS "Date One" values(DATE_TYPE2) AS "Date Two" count by ACCOUNT_NUM
| where count > 1
| eval epoch1=strptime(DATE_TYPE1, "<<format string>>"),
       epoch2=strptime(DATE_TYPE2, "<<format string>>")
| eval DATE_STATUS=if(epoch1=epoch2, "Match", "No Match")

[lgsplunks]

Thank you for this information! I did try this fix but I believe because the "date" fields for these events are actually strings, it didn't fix the problem. I should have clarified that in my question that although I am calling the fields dates, they are strings. However, I am adding your comment to my Splunk notes so I have that helpful knowledge when I am working with dates - I don't think that was covered in any of my initial training.