Splunk Search

Joining with two unrelated tables

aelliott
Motivator

I have two tables

The first table has a list of Categories.
The Second table has a list of Offices.
Such as

Category
Power
Voice
WAN1
WAN2
LAN

Offices:
Office1
Office2
Office3
Office4

I would like to join these 2 csv lookup files such that I get the following as a result:
Power Office1
Power Office2
Power Office3
Power Office4
Voice Office1
Voice Office2
Voice Office3
Voice Office4 etc..

I attempt to join them together and the option of inner, outer, and left all produce the same results. I cannot get it to be a many to many relationship.
I have something like this:

|inputlookup Categories.csv | join [|inputlookup Offices.csv]

Tags (2)
0 Karma
1 Solution

aelliott
Motivator
0 Karma

DalJeanis
Legend

Here's how to do a full cross join in splunk... However, be careful what you wish for...

your first search or file ...
| eval CrossJoinMe = 1 
| join max=0 CrossJoinMe [your second search or file | eval CrossJoinMe = 1 ] 

That code will join every result from the first search or file to each result in the second search or file, subject to all the usual limits (ie, the limit of the number of results form a subsearch, the limits of available memory, etc.)

Note - The field name CrossJoinMe is unimportant. - It's just a field that has the same value for every event on either file.

aelliott
Motivator
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...