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.(?
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.(?
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
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
thanks this was what I was looking for, much appreciated
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