topic Re: What is the best way to correlate events (from same source type) that share a common field value? in Splunk Search

What is the best way to correlate events (from same source type) that share a common field value?

packet_hunter — Fri, 08 Jan 2016 20:01:50 GMT

I am new to splunk so I don't know all the syntax or the most efficient way to construct searches.

My goal is to correlate multiple email events and create a report containing the event fields: Subject, Sender, Recipient, Status (delivered or not delivered).

The problem is that each email event does not contain all the fields I need.
Subject is in a different event.
Sender is in a different event.
Recipient is in a different event.
Status is in a different event.
However, all the events associated with a specific email contain a unique ID (UID), so I can correlate based on the UID.
For example, if I search for an email with a subject = "blah", I can find the UIDs and then search the UIDs.

Primary Search:

index=email  sourcetype=xemail  subject="blah" | table Subject UID

blah 1234
blah 2353
blah 3454

Secondary Search:

index=email  sourcetype=xemail  UID=1234 | table UID Subject Sender Recipient Status

1234   blah
1234          sender@domain.com
1234                             recipient@domain.com
1234                                                     delivered

(Note: if above displayed in a table format, the Subject, Sender, Recipient, and Status fields would be in different columns, not stacked)

As you can imagine manual searching is tedious and time-consuming. So there must be a way to work smarter by using sub search and some function to retrieve all the UIDs from the initial search (where subject = blah) and then correlate all events based on the UIDs.

Please advise if it is even possible to correlate all events based on the initial subject search.

Thank you

Re: What is the best way to correlate events (from same source type) that share a common field value?

jluo_splunk — Fri, 08 Jan 2016 20:30:37 GMT

Have you looked into using the transaction command? This should get the job done, though it may be slow (especially in a large environment.) Documentation here

.. | transaction UID

Re: What is the best way to correlate events (from same source type) that share a common field value?

sundareshr — Fri, 08 Jan 2016 20:31:01 GMT

See if this gives you what your are looking for

index=email sourcetype=xemail | stats list(subject) as sub list(sender) as sender list(recipient) as recp list(status) as status by UID

Re: What is the best way to correlate events (from same source type) that share a common field value?

packet_hunter — Fri, 08 Jan 2016 22:28:56 GMT

Thank you for the comment.

Yes, | transaction UID is a good option to collect all the unique id field values, however the next step is to automate the correlation of all the other events with the same UID.

For instance, for each UID found with search > index=email sourcetype=xemail subject="blah" another search has to be done on each UID to get the other fields: Sender, Recipient, and Delivery Status because as I mentioned before, each field is in a separate event. In other words, the email appliance logs create separate events for the subject, sender, recipient, and delivery status separately. The unique ID what you can use to group them together as a complete picture.

So the next search step is the big question.

Re: What is the best way to correlate events (from same source type) that share a common field value?

packet_hunter — Fri, 08 Jan 2016 22:51:46 GMT

Thank you for the comment.

Unfortunately, this will not display the other fields. Only the subjects with UIDs are listed.

Perhaps I did not explain clearly, because this is a somewhat complicated search.

Imagine you search for an email with subject = Hello in the last 15 minutes.
Lets say you get 10 emails with subject = Hello.

In order to find the sender, recipient, and delivery status another search is required.

The sender, recipient, and delivery status may all be different.
From the first search you can see there are 10 different unique ids displayed per row.
Now a second search of each unique id is required to get the other fields, sender, recipient, and delivery status.

For example one incoming email "session" creates multiple events. Not all fields are in each event, but the UID relates them as part of the same session.

unique ID   subject sender  recipient   delivery status

1 1234 Hello

2 1234 sender@domain.com

3 1234 recipient@domain.com

4 1234 delivered

So this is where I am stuck. How do you take the first results "many" and perform a secondary search on each of the "many" to get the other events to display in one report?

I appreciate everyone's efforts on this.

Tack

Re: What is the best way to correlate events (from same source type) that share a common field value?

Richfez — Fri, 08 Jan 2016 23:04:50 GMT

It may help to see your actual events you need to correlate, but even without that I haven't seen a reason why transaction won't work. My suspicion is you have the transaction command inserted AFTER the filtering, so it can't do the correlation. I'm guessing the below from your existing search snippets, but perhaps try this instead...

index=email  sourcetype=xemail  | transaction UID

If that lumps the events together with matching UIDs, then try

index=email  sourcetype=xemail  | transaction UID | table UID Subject Sender Recipient Status

The key is you don't want to limit your initial search - toss it ALL the events that you'll end up wanting all correlated together, then let Splunk do the correlation. That transaction command will "group" all the events with matching UIDs together.

Re: What is the best way to correlate events (from same source type) that share a common field value?

Richfez — Fri, 08 Jan 2016 23:08:34 GMT

This technique should also work. If you need to search for the "Hello" ones, don't do that until afterwards.

index=email sourcetype=xemail 
| stats list(subject) as sub list(sender) as sender list(recipient) as recp list(status) as status by UID 
| search subject="Hello"

If one of these solves your problem, please remember to "accept" it! IF both do, well, pick the best. 🙂 If neither does, please post back with more info and we can help some more!

(The key in this technique is also as I mention in the "transaction" answer by jluo - give Splunk ALL of the events to work with and group/stats/transaction them, THEN filter.)

Re: What is the best way to correlate events (from same source type) that share a common field value?

Richfez — Fri, 08 Jan 2016 23:23:09 GMT

As below, filter it after you get everything lumped together that needs to be lumped together. Put your | search subject="Hello" at the end of all the above (well, before the table). Like

index=email  sourcetype=xemail  
| transaction UID 
| search subject="Hello"
| table UID Subject Sender Recipient Status

Let us know!

Re: What is the best way to correlate events (from same source type) that share a common field value?

packet_hunter — Mon, 11 Jan 2016 20:19:25 GMT

Thank you !!! It works, I was going about it in the wrong order, and using the wrong method. Lesson learned. I owe you one cyber drink. Thank you again.

Re: What is the best way to correlate events (from same source type) that share a common field value?

sundareshr — Mon, 11 Jan 2016 20:32:43 GMT

Sounds like what you may need is a subsearch

http://docs.splunk.com/Documentation/Splunk/6.1.2/SearchTutorial/Useasubsearch

So in your example, the search would look something like this

<search for other fields>  [ search for ids based on subject | fields UID] | transform ...

Hope this helps

Re: What is the best way to correlate events (from same source type) that share a common field value?

packet_hunter — Mon, 11 Jan 2016 20:33:34 GMT

For some reason this one does not work, even with the search as the last step.

But as a newb, I missed the ordering of the search (d'oh!*?!)

IF and when I discover why this one does not work, I will post the reason.

Thank you to all that helped with this.

Re: What is the best way to correlate events (from same source type) that share a common field value?

packet_hunter — Mon, 11 Jan 2016 21:12:20 GMT

I got it to work by changing [ | search subject ="Hello" ] to [ |search subj ="Hello"]

Thank you