topic Re: Combine two record sets where a distinct value matches (without using join) in Splunk Search

Combine two record sets where a distinct value matches (without using join)

chinchin96 — Tue, 29 Sep 2020 14:15:58 GMT

I have a search that generates two distinct types of record entries (searching for "for event"):

2015-05-05 for event 201216053940303kljdwlj for recipient Geogm
2015-05-05 card 12345678910 for event 201216053940303kljdwlj

Fields I created:

201216053940303kljdwlj = eventid
Geogm = username (it's always 6
characters long and at the end of the
line)
12345678910 = cardnum

I want a table that shows the username and eventid of the first type of record and combines it with the card number where the eventid is a match, showing something like the following:

Time       , username , eventid               , cardnum
2015-05-05 , Geogm    , 201216053940303kljdwlj , 12345678910

I think I got this working using join, but would like a different way to achieve it due to performance issues of using join.

My current query:

index=myindex source="source1" OR source="source2"  "for recipient" 
| extract pairdelim="|;,", kvdelim="=:", auto=f  
| rex field=_raw "(?<username>\s......$)" 
| search username!=""  
| rex field=_raw "event (?<eventid>.(\w+))" 
| search eventid !="" 
| table _time username eventid 
| join eventid [search index=myindex source="source1" OR source="source2"  "for event" 
| extract pairdelim="|;,", kvdelim="=:", auto=f  
| rex field=_raw "card (?<cardnum>.(\w+))"  
| search cardnum !=""  
| rex field=_raw "event (?<eventid>.(\w+))"  
| search eventid !="" 
| table  eventid cardnum]

This is simlar to the question shown here: answers.splunk.com/answers/443909/how-do-i-joincombine-my-two-search-searches-to-get.html?utm_source=typeahead&utm_medium=newquestion&utm_campaign=no_votes_sort_relev but it did not receive an accepted answer

Re: Combine two record sets where a distinct value matches (without using join)

Richfez — Sun, 28 May 2017 00:14:00 GMT

Shouldn't either line 1 or line 8 be "for recipient"?

Re: Combine two record sets where a distinct value matches (without using join)

chinchin96 — Sun, 28 May 2017 00:47:52 GMT

Oops. You're right @rich7177. Just updated it.

Re: Combine two record sets where a distinct value matches (without using join)

somesoni2 — Sun, 28 May 2017 04:44:30 GMT

How about this?

index=myindex source="source1" OR source="source2"  "for recipient" 
| extract pairdelim="|;,", kvdelim="=:", auto=f
| rex field=_raw "event (?<eventid>.(\w+))" 
| search eventid !="" 
| rex field=_raw "(?<username>\s......$)" 
| rex field=_raw "card (?<cardnum>.(\w+))"
| eval timestmap=if(isnotnull(username),_time,null())
| stats values(timestamp) as _time values(username) as username values(cardnum) as cardnum by eventid

Re: Combine two record sets where a distinct value matches (without using join)

woodcock — Sun, 28 May 2017 21:19:57 GMT

Like this:

index=myindex (source="source1" OR source="source2")  ("for recipient" OR  "for event")
| extract pairdelim="|;,", kvdelim="=:", auto=f  
| rex "(?<username>\s......$)" 
| rex "card (?<cardnum>.(\w+))"  
| search username!=""  OR cardnum !="" 
| rex "event (?<eventid>.(\w+))" 
| search eventid !="" 
| stats min(_time) AS Time values(username) AS username values(cardnum) AS cardnum BY eventid

Re: Combine two record sets where a distinct value matches (without using join)

chinchin96 — Mon, 29 May 2017 03:17:33 GMT

Thanks @somesoni2! This worked (after noticing timestamp was mispelled on line 7. :-))

The only issue I saw is that for records in the final display that didn't have a username (not all records do), there was not a timestamp shown even though one exists. I changed isnotnull to isnull and it seemed to work. I then added a

| table _time eventid username cardnum

to the end to get the order presented in the question.

So, while it all works now, I don't know that I could explain how. I'd love to be able to use your method again in the future but don't know I can follow the logic. Can you help me understand it?

Re: Combine two record sets where a distinct value matches (without using join)

chinchin96 — Mon, 29 May 2017 03:21:53 GMT

Thanks @woodcock! This worked as well. Also had to add

| table _time survey_uid partial_email  assigned_card

to get the column order right. So, much like @somesoni2, I love that your method works but I don't kno why. Can you walk me through the logic?

Also, both your query and @somesoni2 seem to be pretty fast. Does Splunk have a preference between the two?

Re: Combine two record sets where a distinct value matches (without using join)

woodcock — Mon, 29 May 2017 14:33:30 GMT

The general approach to a merge is to pull in all the events with a single main search; that is why I put the ("for recipient" OR "for event") there. Then you can do a stats .. BY JoiningField to do the merge. You just need to decide what it is that you need to keep from the merge. doing stats values(*) AS * BY JoiningField gets you pretty much everything and you can trim down from that but in your case you knew exactly what you needed so I worked with that.

I suspect that mine is faster because the other one has 2 search passes but the only way to know for sure is to run each search on your data and use the Job Inspector to see how long each one really takes.

Re: Combine two record sets where a distinct value matches (without using join)

chinchin96 — Mon, 29 May 2017 20:31:43 GMT

Thank @woodcock! This explanation is very useful!