Comparing two field values on different indexes

[dpoloche]

I read through the splunk answers I could find, but I did not find an answer that was helped me with what I need. I attempted using joins, appends, appendcols, stats, etc but could not find the correct way to do this that was not resource intensive. The search below works, but I can't get it to compare the values properly.

index=dbconnect sourcetype=ActivityLog 
       | fields EmailAddress 
       | append [ search index=mail host=mailserver | fields email]

I have email addresses on a mail server which sends emails for multiple applications and therefore has a very large volume of data (index=mail host=mailserver). I also have a smaller application that is a dbconnect index which sends emails out through the SMTP server. My goal is to create alerts to let me know if the application created an email but the mail server did not receive or send it out, so I need to compare the two values.

This also works, but it appears very inefficient: (also for a reason I dont understand the "NOT" matches, while leaving it out does not match..)

| multisearch
     [ search index=dbconnect sourcetype=ActivityLog
       | fields EmailAddress ] 
     [ search index=mail host=mailserver
       | fields email ]
 | where NOT ( EmailAddress = email )

Is there a better way? Again I just need to know when it the field exists in one and not the other. Also I need to know within a period of time, say if the email is not processed by the SMTP server within 30 minutes.

[renjith_nair]

This can be achieved by different methods. Run these searches after making the necessary changes for your environment

Simple but not very efficient

index=dbconnect sourcetype=ActivityLog|fields EmailAddress |search NOT [index=mail host=mailserver |rename email as EmailAddress|fields EmailAddress]

Using eventstats. Its more efficient if you have a common field other than email in both indexes.

( index=dbconnect OR index=mail) (other filed comparisons) | rename email as EmailAddress|eventstats count(EmailAddress) as sentcount by <your other common fields if any>|where sentcount >1

This should group your email address and add count of emailaddresses per a common field. If both indexes has the email address, then the count should be 2 (in other words, finding duplicates method)

Also have a look at this for more examples

https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...