Getting Data In

Can you help me create a transaction using three events?

octavioserpa
New Member

I have a few events, and I need to tie one of them (an event that happens later in my product's transaction) back to the first log of the transaction. The initial problem is that the last log has one type of ID number, and the first log has a different type of ID. I do, however, have an event that always happens in between and contains both ID number types. I would like to create a Splunk query transaction, but I need a little help. I already have the regex I need to extract the values once I can get a transaction

Working my way backwards, here is the final log of the three (I only want to net transactions that have this type of log), then collect the data from their respective first log. This log contains MID (the number following MID, before SPF):

Oct 26 12:44:59 10.x.x.x Splunk_PIApp_Mail_Logs_LVDC: Info: MID 90438452 SPF: mailfrom sender@company.com PermError (v=spf1)

Here is the in-between log that has both id numbers (the MID and the ICID):
Oct 26 12:44:59 10.x.x.x Splunk_PIApp_Mail_Logs_LVDC: Info: Start MID 90438452 ICID 113286802

and finally, here is the very first event which contains the ICID number, and the information I want:
Oct 26 12:44:59 10.x.x.x Splunk_PIApp_Mail_Logs_LVDC: Info: New SMTP ICID 113286802 interface DATAEXT1 (10.x.x.x) address 54.x.x.x reverse dns host sendingserver.sendingdomain.com verified yes

Tags (1)
0 Karma

octavioserpa
New Member

Below is an example full transaction set of events example (I added line number+" - " to the beginning for identification):

Of the the events below, I want to:
1. Find all line #7's for the time period (these contain MID and other fields I want)
2. From the resultant set of line #7's, I want to use the MID field value (in Splunk, it's "internal_message_id") to find any line #4's with said MID (this will give a single event line that has MID and icid)
3. I want to take the resultant set of Line #4's and using the "icid" value, query for their individual Line #1 events (this event has information I need for my table - sender IP for example)
4. I want to then use transaction command, and expect to have the same number of transactions (with three event lines each) as the number of results from step1 (find all line #7's). I expect this because for every #7 type line, there must be a #4 and #1 type line.
5. Once I have this, I can output what I need to a table.

1 - Oct 29 12:09:47 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: New SMTP ICID 32275015 interface DATAEXT1 (10.x.x.x) address x.x.x.13 reverse dns host sendingserver.sendingdomain.com verified yes
2 - Oct 29 12:09:47 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: ICID 32275015 ACCEPT SG UNKNOWNLIST match sbrs[1.0:6.0] SBRS 3.5 country United States
3 - Oct 29 12:09:47 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: ICID 32275015 TLS success protocol TLSv1 cipher DHE-RSA-AES256-SHA
4 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: Start MID 92667296 ICID 32275015
5 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 ICID 32275015 From: <>
6 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 ICID 32275015 RID 0 To:
7 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 SPF: mailfrom identity None
8 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 DMARC: Verification skipped (No record found for the sending domain)
9 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 ready 5476 bytes from <>
10 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 matched all recipients for per-recipient policy DEFAULT in the inbound table
11 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: ICID 32275015 close
12 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 interim verdict using engine: CASE spam negative
13 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 using engine: CASE spam negative
14 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 interim AV verdict using Sophos CLEAN
15 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 antivirus negative
16 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 AMP file reputation verdict : SKIPPED (no attachment in message)
17 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 Outbreak Filters: verdict negative
18 - Oct 29 12:09:48 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: MID 92667296 queued for delivery
19 - Oct 29 12:09:51 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: Delivery start DCID 14225446 MID 92667296 to RID [0]
20 - Oct 29 12:09:52 10.159.10.101 Splunk_PIApp_MailLogs_QDC: Info: Message done DCID 14225446 MID 92667296 to RID [0]

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@octavioserpa,
Try this,

Get the value of missing field to the "other" event using the common field and then join them. For e.g. the first two events have MID but only the second have ICID. So get the ICID also to the first event and then join them with the third event.

index="your index" |"your field extractions" 
|eventstats values(MID) as MID_MISSING by ICID|eval MID=coalesce(MID,MID_MISSING )
|eventstats values(ICID) as ICID_MISSING by MID|eval ICID=coalesce(ICID,ICID_MISSING )
|stats values(_raw), count by MID,ICID

And if you need only those events which have all three events add where count>=3

Happy Splunking!
0 Karma

octavioserpa
New Member

Anyone else have a suggestion?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@octavioserpa,
From the above search,you get all the events which have both MID and ICID in values(_raw). So if you could lets know what's the final output after you get "3" events which are matching, then we can try that directly. Transaction is bit expensive in terms of resource utilization. But if you can't avoid transaction, try this with sample events and lets know if it works.

 index="your index" |"your field extractions" 
 |eventstats values(MID) as MID_MISSING by ICID|eval MID=coalesce(MID,MID_MISSING )
 |eventstats values(ICID) as ICID_MISSING by MID|eval ICID=coalesce(ICID,ICID_MISSING )
|transaction MID,ICID
Happy Splunking!
0 Karma

octavioserpa
New Member

Thanks for your help. Just to make sure I'm clear, let's say that I have millions of log events per day. I first want to capture from our specific index that has the SPF failure (i.e. search for "Info: MID * SPF: mailfrom"). For the transactions that had this event, I want to get the other two logs and create a Splunk "Transaction". When I ran what you provided, it did not seem to create a transaction. I expected to see all three events together.

Would it be possible for you to help me plug my actual log files into this? If I can get them in a transaction, I'll be able to regex out the data I need to a table. Fingers crossed, and thanks in advance!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...