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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...