topic Compare if an event in different sourcetypes has the same values for a combination of fields in Splunk Search

Compare if an event in different sourcetypes has the same values for a combination of fields

btiggemann — Mon, 28 Sep 2020 17:46:21 GMT

Hey Splunkers,

We want to track an email communication which is done over multiple servers with multiple log formats.
We have three fields that must have the same value in an event in both sourcetypes, if this is true we want to mark the email as accepted.

The field combination that must match are from, to, subject

here is an example:

the log event from the anti spam gateway:
Oct 1 10:08:05 10.1.8.75 date=2014-10-01 time=10:08:05 device_id=FEVM020000026428 log_id=0200014307 type=statistics pri=information session_id="s91884r7014306-s91884r8014306" client_name="xxx.mail.de [5.123.123.123]" dst_ip="10.1.8.75" **from=bla@foo.de to=test@ymca.de* polid="0:1:1" domain="ymca.de" subject="TEST" mailer="mta" resolved="OK" direction="in" virus="" disposition="Accept" classifier="Not Spam" message_length="1697"*

the log event from the receiving mail server:
[2014-10-01 17:20:21.555] [ 219316 ms] [Snr- 4169584] [connection13] [INFO ] typ="mail statistic" Snr=4169584 **from=bla@foo.de to=test@ymca.de subject="TEST"* disposition=accept direction=in*

We need a table with the following values:

from to subject AntiSpamOK MailrcvdOK
bla@foo.de test@ymca.de TEST 1 1
mail2@test.de test@ymca.de NOT GOOD 1 0

With that we can filter if a mail is processed or not. for example |where MailrcvdOK=0

I have tried the following:

eventtype="Antispam_Inbound_Mail" |stats count AS AntiSpamOK by from, to, subject | join type=left from to subject [search eventtype="Mailserver_Inbound_Mail" |stats count AS MailrcvdOK by from, to, subject ] |eval MailrcvdOK =if(isnull(MailrcvdOK ),0,MailrcvdOK) |fields from to subject AntiSpamOK, MailrcvdOK

I have used a left join, because only the mails in the first search must seen definitely on the second one.

But with this command I don't see any matching row where AntiSpamOK and MailrcvdOK are both 1.
I have checked the logs, in both logs are event combination with the same field values. I just don't know how to get it visible.
I also tried this with the |set intersection command without any success.

Is there a better way to do it? Am I wrong with something?

Thanks to all

Re: Compare if an event in different sourcetypes has the same values for a combination of fields

lguinn2 — Thu, 02 Oct 2014 00:12:55 GMT

Try this

eventtype="Antispam_Inbound_Mail" OR eventtype="Mailserver_Inbound_Mail"
| stats count(eventtype="Antispam_Inbound_Mail") as AntiSpamOK count(eventtype="Mailserver_Inbound_Mail") as MailrcvdOK
      by from to subject

This avoids the subsearches, which may be hitting limits and causing the problems. It should also run much faster.

Re: Compare if an event in different sourcetypes has the same values for a combination of fields

btiggemann — Mon, 28 Sep 2020 17:47:22 GMT

Hi Lisa,

it looks like this is it. Thanks for your help and giving a good idea.

I have adjusted the search to:

eventtype="AntiSpam_Inbound_Mail" OR eventtype="Mail_Inbound_Mail" | stats count(eval(eventtype="AntiSpam_Inbound_Mail")) as AntiSpamOK count(eval(eventtype="Mail_Inbound_Mail")) as MailrcvdOK by subject |eval Mailfactor=MailrcvdOK-AntiSpamOK

I was wondering why MailrcvdOK and AntiSpamOK are always "0". It is because you have to use an eval in the count() function.

Now we have got another problem:

Sometimes a mail is processed several times in the log because of a mail loop or queuing error. With that the combination of from to subject can occur 3 time in "AntiSpam_Inbound_Mail" and one time in "Mail_Inbound_Mail".

For example:

from to subject AntiSpamOK MailrcvdOK
bla@foo.de test@ymca.de TEST 3 1
bla1@foo.de tes1t@ymca.de TEST 7 1

With your search we are not able to see that reprocessed mails.
Is there a chance to track this, too?

Thanks for your help again.

Re: Compare if an event in different sourcetypes has the same values for a combination of fields

btiggemann — Wed, 08 Oct 2014 23:41:41 GMT

We fixed it using eventstats instead of stats... At the end it was very easy...