Join search with multi-values

[pbarford]

I have a join on two searches, from the first search, the data return is the same as the following table (equivalent of running this)

source="/home/pbarford/tmp/300913/resequencer/reseq01-application.2013-09-30.log" | search "Expiry" | rex "[nike.(?[0-9]{1,45})]" | rex max_match=20 "L[(?[0-9]{1,45})]" | fields eventid, seqno | table eventid, seqno

eventid, seqno
1 22
45
67
2 2
3 5

So I want is to take the eventid and seqno and join it to the next query. Problem is that a join on eventid "1", as shown above, is not being done. For eventid 2 & 3 the join is being done. I am assuming this is due to the fact that for 1 their are multi-values in the seqno column. What is the best way around this problem?

The full query is below

sourcetype="logtype1" | search "Expiry" | rex "[nike.(?[0-9]{1,45})]" | rex max_match=20 "L[(?[0-9]{1,45})]" | fields eventid, seqno | join eventid, seqno [ search sourcetype="logtype2" "Inserted" | rex "EventId: (?\d+)" | rex "SeqNo: (?\d+)" | rex "Duration: (?\d+)" | fields eventid, seqno, duration ] | table eventid, seqno, duration

[wpreston · Accepted solution]

Try using mvexpand, which will make an individual event out of all the combinations of eventid and seqno for each record in your table, i.e.:

eventid      seqno
-------------------
1            22
             45
             67
2            2
3            5

becomes:

eventid      seqno
-------------------
1            22
1            45
1            67
2            2
3            5

So your search would be:

sourcetype="logtype1" "Expiry" 
| rex "[nike.(?<eventid>[0-9]{1,45})]" 
| rex max_match=20 "L[(?<seqno>[0-9]{1,45})]" 
| fields eventid, seqno 
| mvexpand seqno 
| join eventid, seqno [ search sourcetype="logtype2" "Inserted" 
| rex "EventId: (?<eventid>d+)" 
| rex "SeqNo: (?<seqno>d+)" 
| rex "Duration: (?<duration>d+)" 
| fields eventid, seqno, duration ] 
| table eventid, seqno, duration

[pbarford]

thanks this was what I was looking for, much appreciated

[alacercogitatus]

I think a subsearch might work more effectively. Use the logtype1 search to search the logtype2 events. The format returned from the subsearch should be (eventid=1 AND seqno=22) OR (eventid=1 AND seqno=45) ...

sourcetype="logtype2" "Inserted" | rex "EventId: (?<eventid>d+)" | rex "SeqNo: (?<seqno>d+)" | rex "Duration: (?<duration>d+)" | search [search sourcetype=logtype1 "Expiry" | rex "[nike.(?<eventid>[0-9]{1,45})]" | rex max_match=20 "L[(?<seqno>[0-9]{1,45})]" | fields eventid, seqno ] | table eventid, seqno, duration