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