Splunk Search

Join search with multi-values

pbarford
Explorer

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

Tags (3)
0 Karma
1 Solution

wpreston
Motivator

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

View solution in original post

wpreston
Motivator

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
Explorer

thanks this was what I was looking for, much appreciated

0 Karma

alacercogitatus
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...