Splunk Search

How to get all possible entries from two lookups?

Communicator

How to get all possible entries from two lookups? For instance, lookup_1 and lookup_2

lookup_1

    application
    =========
    app1
    app2
    app3

lookup_2

    service      link      
    ======     ==============
    s1              link1
    s1              link2
    s2              link3

And the end result should be:

lookup_3

application     service        link
==========      ======       ====================
app1                s1                link1
app1                s1                link2
app1                s2                link3
app2                s1                link1
app2                s1                link2
app2                s2                link3
app3                s1                link1
app3                s1                link2
app3                s2                link3
1 Solution

Contributor

Give this a go ...

| inputlookup csv1.csv | append [|inputlookup csv2.csv] | eventstats values(application) AS application | search link=* | mvexpand application

View solution in original post

SplunkTrust
SplunkTrust

There's a weird way to do this without using either the join or the append command. Posting it as an answer partly because it's fun, partly because join and append are.... evil? If you're doing lookups that only have a small number of rows join and append answers are probably the right answers though.

Anyway, here it is.

| inputlookup lookup_1 | inputlookup lookup_2 append=t | eventstats values(application) as applications | search service=* OR link=* | mvexpand applications | rename applications as application

the append=t option on the inputlookup command is better to use than the more generic append command, in particular I believe it has no limit on number of rows being appended (append and join will throw away rows after 50K).
The funny looking eventstats command is going to put a multivalue field called "applications" on every row in the entire result set, where the multivalue values are each all of the values of application. The search clause then throws away the actual "lookup_1" rows that we had. Lastly we mvexpand and rename, and the net result is what you need - the sort-of cross-product of the two lookups.

0 Karma

Champion

maybe use join to put them together by creating a common field for each search.

| inputlookup app_lookup.csv | eval join_field="temp" | join join_field max=0 [|inputlookup link_lookup.csv | eval join_field="temp"] | fields - join_field
0 Karma

Contributor

Give this a go ...

| inputlookup csv1.csv | append [|inputlookup csv2.csv] | eventstats values(application) AS application | search link=* | mvexpand application

View solution in original post

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!