Splunk Search

How to get all possible entries from two lookups?

splunkrocks2014
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

lquinn
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

sideview
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

maciep
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

lquinn
Contributor

Give this a go ...

| inputlookup csv1.csv | append [|inputlookup csv2.csv] | eventstats values(application) AS application | search link=* | mvexpand application
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 ...