title: Splunk - Match different fields in different events from same data source
body: <p>I have a data source in which I need to return all pairs of events (event1, event2) from a single data source, where field1 from event1 matches field2 from event2.</p>
  
  <p>For example, let's say I have the following data.</p>
  
  <p><a href="https://i.stack.imgur.com/WcZkl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WcZkl.png" alt="enter image description here"></a></p>
  
  <p>I need to return a pair of events where field <strong>id</strong> from event1, matches field <strong>referrer_id</strong> from event2. Let's say, to get the following report.</p>
  
  <ul>
  <li>Adam Anderson referred Betty Burger on 2016-01-02 08:00:00.000</li>
  <li>Adam Anderson referred Carol Camp on 2016-01-03 08:00:00.000</li>
  <li>Betty Burger referred Darren Dougan on 2016-01-04 08:00:00.000</li>
  </ul>
  
  <p>In sql I can do this quite easily with the following command.</p>
  
  <pre><code>select a.first_name as first1, a.last_name as last1, b.first_name as first2,
         b.last_name as last2, b.date as date
  from myTable a
  inner join myTable b on a.id = b.referrer_id;
  </code></pre>
  
  <p>Which returns the following table,</p>
  
  <p><a href="https://i.stack.imgur.com/eMoZE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eMoZE.png" alt="enter image description here"></a></p>
  
  <p>which gives exactly the data I need.</p>
  
  <p>Now, I've been attempting to replicate this in a splunk query and have run into quite a few issues. First I attempted to use the transaction command, but that aggregated all of the related events together as opposed to matching them a pair at a time.</p>
  
  <p>Next, I attempted to use a subsearch, first finding the <strong>id</strong> and then searching in the subsearch, first for the first event by <strong>id</strong> and the appending the second event by <strong>referral_id</strong>. Then, since <strong>append</strong> creates a new row instead of appending to the same row, using a <strong>stats</strong> to aggregate the resulting rows by the matching id field. I did attempt to use <strong>appendcols</strong> but that didn't return anything for me.</p>
  
  <pre><code>...
  | table id
  | map search="search id=$id$
    | fields first_name, last_name, id
    | rename first_name as first1
    | rename last_name as last1
    | rename id as match_id
    | append [search $id$
      | search referral_id=$id$
      | fields first_name, last_name, referral_id, date
      | rename first_name as first2
      | rename last_name as span2
      | rename referral_id as match_id]"
  | fields first1, last1, first2, last2, match_id, time
  | stats values(first1) as first1, values(last1) as last1, values(first2) as first2,
    values(last2) as last2, values(time) as time by id
  </code></pre>
  
  <p>The above query works for me and gives me the table I need, but it is incredibly slow due to the repeated searches over the entire time frame, and also limited by the map <strong>maxsearches</strong> which, for whatever reason, cannot be set to unlimited.</p>
  
  <p>This seems like an overly complicated solution, especially in comparison to the sql query. Surely there must exist a simpler, faster way that this can be done, which isn't limited by the arbitrary limited settings or the multiple repeating search queries. I would greatly appreciate any help.</p>