topic Re: [Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values? in Splunk Search

[Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values?

PierreE — Tue, 16 Jun 2015 11:54:50 GMT

Hello,

My problem is that I have ironports mail logs splitted like this :

Jun  8 13:51:21 my_server: Mon Jun  8 13:46:14 2015 Info: New SMTP ICID 123456789 interface Data 1 (1.2.3.4) address 10.10.10.10 reverse dns host blabla.mail.com verified yes
Jun  8 13:51:21my_server: Mon Jun  8 13:46:14 2015 Info: ICID 123456789 ACCEPT SG UNKNOWNLIST match sbrs[-1.5:7.0] SBRS 5.6
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:14 2015 Info: Start MID 987654321 ICID 123456789
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:14 2015 Info: MID 987654321 ICID 351684134 From: <test_name@mail.fr>
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:14 2015 Info: MID 987654321 ICID 351684134 RID 0 To: <test_name2@mail.fr>
Jun  8 13:51:21my_server: Mon Jun  8 13:46:15 2015 Info: MID 987654321 Message-ID '<id@mail.fr>'
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:15 2015 Info: MID 251913918 Subject 'test_subject'
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:15 2015 Info: MID 987654321 ready 18615 bytes from <test_name@mail.fr>
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:15 2015 Info: MID 987654321 rewritten to MID 987654322 by LDAP rewrite
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:15 2015 Info: MID 987654322 ICID 0 From: <test_name@mail.fr>
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:15 2015 Info: MID 987654322 ICID 0 RID 0 To: <test_name2@mail.fr>
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:15 2015 Info: MID 987654322 attachment 'image001.jpg'
Jun  8 13:51:21 my_server: Mon Jun  8 13:46:15 2015 Info: MID 987654322 attachment 'image001.jpg'

And I want to create an aggregate events that is able to join a log that links IP address to preliminary MID and then can handle field MID going multi-value in a single event that shows the mapping of preliminary MID to final MID and includes all the later events that have only the final MID

The goal is for example extract IP address which send an email with a picture in attachment.

I tryed some transactions to join ICID, MID like :

index=test_ironport sourcetype=cisco:esa:textmail (ACCEPT OR address OR attachment_type=doc OR MID OR vendor_action=mid_rewritten) | eval courant_mid=if(isnotnull(prev_internal_message_id), prev_internal_message_id, internal_message_id)| transaction icid courant_mid

index=test_ironport sourcetype=cisco:esa:textmail (ACCEPT OR address OR attachment_type=doc OR MID OR vendor_type=mid_rewritten) | transaction icid internal_message_id prev_internal_message_id

The issue in my case is the changing MID that complicate the joint of linked events

I wonder if I have to keep using transaction or do I have to change the method and use lookup

May be someone is aware of an App which can correlate Ironport email events at the indexing ?

Thank you,
Pierre

Re: [Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values?

woodcock — Tue, 16 Jun 2015 14:49:25 GMT

Assuming that there is only a single LDAP rewrite per transaction, this should work.

Create a lookup definition for the MID ONCE like this in transforms.conf:

[MID_lookup]
filename = MID_lookup.csv

Run this search FIRST (every time) to create a lookup file:

index=test_ironport sourcetype=cisco:esa:textmail vendor_action=mid_rewritten| rex "\s+Info:\s+MID\s+(?<TempMID>\d+)\s+rewritten to\s+(?<FinalMID>\d+)\s+by LDAP rewrite" | eval InitialMID = TempMID . "," . FinalMID | fields - TempMID | makemv delim="," InitialMID | mvexpand InitialMID | outputlookup MID_lookup.csv

Then run this search to use the lookup:

index=test_ironport sourcetype=cisco:esa:textmail (ACCEPT OR address OR attachment_type=doc OR MID) | rex "^.*?MID\s+(?<InitialMID>\d+)" | lookup MID_lookup InitialMID OUTPUT FinalMID | transaction icid FinalMID

http://docs.splunk.com/Documentation/Splunk/6.2.3/SearchReference/Outputlookup

Re: [Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values?

aljohnson_splun — Tue, 16 Jun 2015 17:06:16 GMT

Have you considered capturing the second mid, e.g.

| rex "MID \d+ rewritten to MID (?<second_mid>\d+)

then using it in your transaction

index=test_ironport sourcetype=cisco:esa:textmail 
(ACCEPT OR address OR attachment_type=doc OR MID OR vendor_action=mid_rewritten) 
| eval courant_mid=if(isnotnull(prev_internal_message_id), prev_internal_message_id, internal_message_id)
| rex "MID \d+ rewritten to MID (?<second_mid>\d+)
| transaction mid second_mid icid

or something else along those lines?

Re: [Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values?

PierreE — Wed, 17 Jun 2015 12:30:27 GMT

No it doesn't work... I have quite the same results

Re: [Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values?

PierreE — Wed, 17 Jun 2015 12:30:55 GMT

Didn't change of my results...

Re: [Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values?

woodcock — Wed, 17 Jun 2015 14:42:04 GMT

Will you post the results of each of the 2 searches (remove the | outputlookup) for a very tiny sample of data? I have double-checked and it definitely should work...???

Re: [Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values?

woodcock — Tue, 07 Jul 2015 21:34:09 GMT

Did you ever get this to work?

Re: [Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values?

PierreE — Wed, 08 Jul 2015 06:53:49 GMT

Sorry, i tried but now i'm on something new at work so... I'll be on this another time !

Re: [Cisco ironport mail logs] How can I do a transaction on a sometimes-multi-value field where a key field migrates values?

kcambron — Wed, 02 Sep 2015 16:43:48 GMT

The following works for me, but I would like to automate this process somehow for a dashboard. I don't think an automatic lookup would work if the MID is reused for later messages, but if there is a way to insert a pause (or delay) between the generation of the csv and running the search that would maybe be an option for running the two parts as one query.

Run this before every search to create the updated csv:

index=cisco sourcetype=cisco:esa:textmail source=/log/sources/ironport/ironport.log  vendor_action=mid_rewritten | rex field=_raw " Info: MID (?\d+) rewritten to MID (?\d+) by " | eval InitialMID = TempMID . "," . FinalMID | fields - TempMID | makemv delim="," InitialMID | mvexpand InitialMID | fields + InitialMID,FinalMID | fields - _raw,_time | outputlookup MID_lookup.csv

This is the search:

index=cisco sourcetype=cisco:esa:textmail source=/log/sources/ironport/ironport.log | rex "^.*?MID\s+(?\d+)" | lookup MID_lookup.csv InitialMID OUTPUT FinalMID | transaction FinalMID

Running the two searches together as I would like to do creates broken files with blank spaces and incomplete data since the csv isn't fully generated before the search runs.

Thoughts?

Thanks.