Splunk Search

JOIN on some of the columns

michael_vi
Path Finder

I have a table (that is a spitted URL) in the following format:

 

field1field2field3field4field5field6
aaaaa11111qqqqqaaaaaattttttyyyyyy
aaaaa11111ccccccrrrrrrr  
bbbbb22222rrrrrrrrriiiiiiiiiiivvvvvv 
ccccc22222wwwwwttttttttt  
ddddd333334444445555555  

 

Ans the other table has only some of the columns:

field1field2field3field4NameDescription
ccccc22222  MickeyMouse
aaaaa11111  PinkyBrain
ddddd33333444444 ZZTop

 

I need that the rows in the second table with be marched to first one, when the values of the second table have only a "base" values. This is what I expect to get:

field1field2field3field4field5field6NameDescription
aaaaa11111qqqqqaaaaaattttttyyyyyyPinkyBrain
aaaaa11111ccccccrrrrrrr  PinkyBrain
aaaaa11111qqqqqaaaaaattttttyyyyyyPinkyBrain
aaaaa11111ccccccrrrrrrr  PinkyBrain
bbbbb22222rrrrrrrrriiiiiiiiiiivvvvvv ZZTop
ccccc22222wwwwwttttttttt  MickeyMouse
ddddd333334444445555555  ZZTop

 

| join type=left field1 field2... []

It make sense that when I do a left join it looks for a corresponding values in all fields, and if it's not there... I have no results

How can I solve it?
Thanks

Labels (1)
Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It is not clear how you get your expected results.

Why are the first two lines repeated?

Why is bbbbb ZZ Top?

0 Karma

michael_vi
Path Finder

@ITWhisperer 

They are repeated, since it answers the JOIN condition.

aaaaa11111

 

Can be applied to:

field1field2field3field4field5field6
aaaaa11111qqqqqaaaaaattttttyyyyyy
aaaaa11111ccccccrrrrrrr  

 

The only way, I see that it can be done, is by each time to make a another join... something like:

| join type=left max=0 field1 [...]
| join type=left max=0 field1 field2 [...]
| join type=left max=0 field1 field2 field3 [...]

I was looking for more elegant solution

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This still doesn't explain why bbbbb "joins" with ZZ Top?

0 Karma

michael_vi
Path Finder

@ITWhisperer 

You're right. My mistake. Should be NULL

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

 

search 1
| append [ search 2 ]
| stats values(*) as * by field1 field2
| mvexpand field3
| mvexpand field4

 

0 Karma
Get Updates on the Splunk Community!

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...

Enterprise Security Content Update (ESCU) | New Releases

In October, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Monitoring MariaDB and MySQL

In a previous post, we explored monitoring PostgreSQL and general best practices around which metrics to ...