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
Path Finder

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

OR

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

1 Solution

kcambron
Engager

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.

View solution in original post

0 Karma

kcambron
Engager

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.

0 Karma

aljohnson_splun
Splunk Employee
Splunk Employee

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?

0 Karma

PierreE
Path Finder

Didn't change of my results...

0 Karma

woodcock
Esteemed Legend

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

0 Karma

PierreE
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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...???

0 Karma

woodcock
Esteemed Legend

Did you ever get this to work?

0 Karma

PierreE
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...