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
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
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
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
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.
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.)
I got it to work by changing [ | search subject ="Hello" ] to [ |search subj ="Hello"]
Thank you
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
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
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.
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.
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!
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.