topic What is the best way to join/combine/correlate fields from separate events with separate UIDs? in Splunk Search

What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Wed, 16 Mar 2016 18:41:33 GMT

Scenario:
I am searching email event logs. I can find some of the needed fields by a unique id (UID) and I find some fields by diffferent unique id (X-UID). Some events contain both UID and X-UID but not all the fields I need.

Here is a sample of the code:

[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID] 
    |stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by UID 
[search index=mail sourcetype=xemail sender = "sender@domain.com" |stats count by XUID| fields XUID] 
    |stats list(dest) as dest_ip list(sender) by XUID

Ultimately I would like results to show
subj sender recp status dest_ip

Thank you

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

martin_mueller — Wed, 16 Mar 2016 20:01:28 GMT

Some sample data would help.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

lguinn2 — Wed, 16 Mar 2016 20:14:39 GMT

Try this - I don't think it is exactly what you want, but it should be closer

index=mail sourcetype=xemail 
| stats values(subject) as subject values(sender) as sender values(recipient) as recipient 
     values(vendor_action) as status values(dest_ip) as dest_ip by UID
| append [ search index=mail sourcetype=xemail 
     | stats values(subject) as subject values(sender) as sender values(recipient) as recipient 
          values(vendor_action) as status values(dest_ip) as dest_ip by XUID ]

Your original search seems overcomplicated.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Wed, 16 Mar 2016 21:08:57 GMT

Thank you for the suggestion, I will give it a try.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Wed, 16 Mar 2016 21:14:02 GMT

I made a mistake the Key is "sender".

The logs I need to join/correlate will have the sender in common.

For example
Fields UID are subject, sender, recipient, vendor_action
Fields XUID are sender, dest

Unfortunately I can release the actual data.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

martin_mueller — Wed, 16 Mar 2016 22:23:12 GMT

So, if they share the sender field, how do you distinguish two conversations by the same sender?

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Fri, 18 Mar 2016 14:13:43 GMT

Hi Lisa,
To explain the apparent "over-complication". The fields I want are in separate events, so I need a key (e.g. UID) to correlate all the events from a specific email session, giving me results with subj, sender, recipient, etc. with the same UID. IF you know a better way to accomplish that I will definitely try it.

So with this question, specifically some fields I want are in events with UID and others are in events with XUID. Therefore I need to find the key (e.g. sender) that correlates to the other events without XUID.

Its complicated because the events don't contain all the fields I need.

I hope that makes sense.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

snoobzilla — Tue, 29 Sep 2020 09:08:07 GMT

How about using the transaction command on sender?

yoursearch | transaction mvlist=1 sender | table _time subject sender UID X_UID

You may want to through a fillnull command in there. You could also add another case/coalesce based field if you need to conditionally include based on UID X_UID.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Fri, 18 Mar 2016 14:54:19 GMT

Hi Martin,

Thank you for assisting with this.
The email logs I analyze contain multiple events per session, and the fields I want, need to be correlated from all the events related to that email session. For example the sender is in one event, the subject is in a different event, the recipient is in a different, but they all share a field UID value. That is the reason for this part of the code.

[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID]
|stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by UID

Now I have other fields values that I need from other separate events that share a field XUID value. The trick is trying to correlate all events that share UID value and XUID value. I have not found the right key. There are events that contain both the UID and XUID fields but I have not figured out how to grab all the fields with from that event.

I hope that makes sense, if not I will try to mock up some event logs.

Thank you

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Fri, 18 Mar 2016 14:55:41 GMT

Thank you I will give it a try.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

lguinn2 — Fri, 18 Mar 2016 16:29:04 GMT

 index=mail sourcetype=xemail UID=* OR XUID=* sender=*
 | stats list(recipient) as recipient  list(subject) as subject  list(vendor_action) as status 
         list(dest_ip) as dest_ip list(UID) as UID list(XUID) as XUID by sender

I would avoid the use of the transaction command if there is a large number of events.
This search is very fast and simple. Use "values" instead of "list" in the stats command if you want remove duplicates from the results. Add "_time" at the end of the stats command if you want to show the time that each event occurred.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

martin_mueller — Fri, 18 Mar 2016 21:44:00 GMT

For multiple-chained-keys transactions the transaction command should be ideal. What happens if you run this?

index=mail sourcetype=xemail | transaction UID XUID | table _time duration subject sender recipient vendor_action dest

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Mon, 21 Mar 2016 17:29:06 GMT

Thank you, I will give it a try, however there are a large number of events so I will have to [subsearch] first and then use transaction otherwise it will take too long.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

martin_mueller — Mon, 21 Mar 2016 21:19:34 GMT

Use a short time range for testing.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Tue, 22 Mar 2016 16:00:58 GMT

Thank you for the advice, using the short time range, however I was aware of that. The problem is that "transaction " is just too expensive.

But perhaps I should rephrase my question for you again.
So I am working with email logs. The logs are such that subject, sender, recipient, attachment, etc are all in separate events that share a unique ID (uid). If I use:

[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID] 

|stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by UID

I will get some of the fields that I want (e.g. subject, sender, recipient, status, etc.).

However there are other fields I want that are not associated with UID but rather XUID. If I use:

    [search index=mail sourcetype=xemail sender = "Blah" |stats count by XUID| fields XUID] 

    |stats list(dest) as dest_ip list(recipient) as recp by XUID

I will get some other fields I want (e.g. dest_ip).

There are events that contain both UID and XUID, but I don't know how to create a secondary search to pull up all the events containing the XUID found by the primary search of the UID.

Bottom line I am looking for a way to search a subject and get all the fields associated with the UID, which includes the XUID. Then use the XUID results to find all fields associated with the XUID number to combine all fields by UID and XUID for that email session.

If I am going about this the hard way and you have a better solution please let me know.

Thank you

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

martin_mueller — Tue, 22 Mar 2016 21:24:14 GMT

Re your long comment on the question: That's exactly what transaction does, even spanning multiple chained ID fields.

Here's a working example:

| stats count as raw | eval raw = "subject=foo uid=123
subject=foo2 uid=321
sender=bar2 uid=321 xuid=cba
sender=bar uid=123 xuid=abc
recipient=baz xuid=abc
recipient=baz2 xuid=cba" | makemv delim="
" raw | mvexpand raw | rename raw as _raw | extract
| eval _time = time()-(random()%1000) | sort - _time
| transaction uid xuid | table _time duration eventcount subject sender recipient uid xuid

Make sure you keep the line breaks as they are here, that's important for this dirty kind of dummy data generation from within the search bar.

First I set up six events, three events per email, each event containing only one "email-y" field.
The events for subject and sender are tied together with uid, the events for sender and recipient are tied together with xuid, and the event for sender ties together the uid and the xuid giving you a nice transitive transaction.

If you want to search for subject, sender, etc before building the transaction you can either do that manually:

The good case: You have an event with the field to filter by (say, sender) and both ID fields.

  index=mail sourcetype=xemail
[ search index=mail sourcetype=xemail sender=foo | fields UID XUID | dedup UID XUID | format "(" "(" "OR" ")" "OR" ")" ]
| transaction UID XUID

That will search for events matching your sender and use the UID and XUID field to search all potential matches beyond the "sender-event", then build the transaction from there.

The bad case, #1: You have an event with the field to filter by, but only the UID field.

  index=mail sourcetype=xemail
[ search index=mail sourcetype=xemail 
  [ search index=mail sourcetype=xemail sender=foo | fields UID | dedup UID ]
  | fields UID XUID | dedup UID XUID | format "(" "(" "OR" ")" "OR" ")" ]
| transaction UID XUID

The innermost subsearch will go in, search for your sender, and come back with a list of UIDs. Those are inserted into the outer subsearch, that will go and retrieve all events with that UID - some of those will have the missing XUID! From there it proceeds like the good case above, using UID or XUID to collect together all relevant events and run the transaction.

The bad case, #2: You have an event with the field to filter by, but only the XUID field.
This works like the bad case #1, but you need to add two Xs to the innermost subsearch... so when filtering, you need to know which of the two bad cases to run 😞

Even though the two bad cases mean you have to go through your index thrice, each run should be a fairly rare search. This will be slower if you search for a sender that sent 90% of all emails, mind.

Instead of building those subsearch-monsters manually, there is a much-forgotten search command searchtxn to do just that for you (I think, don't have data handy to actually test).
To use that, you first have to set up a transaction type in transactiontypes.conf like this:

[xemail]
fields = UID, XUID
search = index=mail sourcetype=xemail

To confirm that this type works, run a regular non-filtering search with | transaction name=xemail and see that it returns the same things as manually specifying the fields. Once that's done, run this with nothing else in the search bar:

| searchtxn xemail sender=foo

That should collect together all the required IDs and neatly return only matching transactions without scanning everything.

http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/searchtxn

One caveat about searchtxn, it's not going to honour your time range picker.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Wed, 23 Mar 2016 13:23:03 GMT

Thank you Martin for enduring my long response and providing the code. I will try it and let you know.

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

packet_hunter — Wed, 23 Mar 2016 19:58:01 GMT

Thank you Martin, I see your strategy, however my objective was to search by one field value, like subject = blah, and automate the subsequent search that would produce the other needed fields. With your suggestion I would need to lookup the uid and xuid with multiple searches. I am trying to avoid the tedious/manual multiple searches.

Do you know how to search for a field value and use the result to automatically launch a subsequent search based on the returned value? That is where I am stuck.

Thank you

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

lguinn2 — Wed, 23 Mar 2016 20:05:24 GMT

All I think you need to do is to add sender to the list of fields for transaction:

| transaction sender uid xuid | table _time duration eventcount subject sender recipient uid xuid

Re: What is the best way to join/combine/correlate fields from separate events with separate UIDs?

lguinn2 — Wed, 23 Mar 2016 20:14:24 GMT

Or perhaps, assume that you have used a form to collect the value of the subject field and have stored in a token named $subject$
Your search could be

index=mail sourcetype=xemail UID=* OR XUID=* sender=* subject="$subject$*"
| transaction sender uid xuid 
| table _time duration eventcount subject sender recipient uid xuid

You could use any field, not just subject. However, the trick to this search is that first you retrieve only the events that have the field of interest. Then use transaction to group them based on the sender and the uids. Note that sender is actually the only field that you named as being part of both events with UID and events with XUID. So sender needs to be part of the transaction command.